# Counting number of blank cells until value found in cell

#### jnutt

##### New Member
My cell range is "E4:AJ4". These cells are either blank or have a "1" in them. I need to be able to count from "E4" until the first "1" is read and I need to display this value. If there is a "1" in "E4" I need to display a "0"; If there is a "1" in "J4" I need to display a "5"; etc. My programming is not strong and I have not been able to find examples close enough to meke this work. Any help would be appriciated.

Thanks
Jack

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Greg Truby

##### MrExcel MVP
=MATCH(1,E4:AJ4,0)-1

{Edit}Doggone it -- I forgot my manners for the umpteenth time...Welcome to the board! {EndEdit}

#### AlohaJulio

##### Board Regular
Welcome to the board.

I think you can use the match function
=MATCH(1,E4:AJ4,0)-1
the -1gets you to start counting at 0 for column E

EDIT: nicely done Greg, cheers

#### andreashak

##### New Member
Welcome to the board.

I think you can use the match function
=MATCH(1,E4:AJ4,0)-1
the -1gets you to start counting at 0 for column E

EDIT: nicely done Greg, cheers

Hey guys I have a similar problem with a row of numbers.
The series is either (annual 1 number every 12 columns, semiannual number every 6, quarterly number every 3 columns, monthly number every column or none remaining in which case there would be no numbers between the current cell and the last cell in the series).
Unfortunately the number that may be found in the series is not constant and therefore I am looking for any number.
The actual function is converting a % from one of the series (annual, semiannual, quarterly, monthly) types into a monthly % series. (1+number)^(1/12)-1

I hope this is clear enough for someone. Unfortunately I cant upload an excel example from work. Sorry.

#### andreashak

##### New Member
Ok I am possibly the dumbest person ever.
I used the following formula

=IF(ISBLANK(F37),E63,CHOOSE(\$E\$32,(1+F37)^(1/12)-1,(1+F37)^(6/12)-1,(1+F37)^(3/12)-1,F37))

Basically if the cell in the series (corresponding to the date in the result series) is blank, then get the last cell value in the result series, if its not blank choose the method to convert the above reference to a monthly rate dependant on another drop down.

Thanks.

#### nss123

##### New Member
Thank you for your response. I have a situation below

I have 12 months Jan - December in columns. Contractors with salaries are in the row.
columns B C D E F G H I J K L M
jan Feb mAR apr May Jun Jul Aug Sep Oct Nov Dec
ROW
4 Name 1. 4000 5000 1000
5 Name 2 1000 2000

I am looking for a countblank till I see a dollar/numbers in Cell. For example Name 1 has Jan, Feb, Mar Blank before April , so the count will be 3 blanks.

Thank you

=MATCH(1,E4:AJ4,0)-1

{Edit}Doggone it -- I forgot my manners for the umpteenth time...Welcome to the board! {EndEdit}

#### Cargueta

##### New Member
Dear all,

I was wondering if you could help me out with a similar problem.

I have a calendar in Excel, where I would like to mark the start and end of a time lapse, but I would also like to only count the business days.

 NB BD BD BD BD NB NB NB BD BD BD BD NB NB BD Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Start End

<tbody>
</tbody>

So, NB means not a business day, as you can see the Sat and Sun are NB, and there is also a holiday on Monday 8.

Would it be possible to count with a formula (COUNTIF, SUMIF, etc.) the working days between the Start and the End?

Please let me know if you could help me out.

Best regards,
Carlos

Replies
1
Views
201
Replies
4
Views
98
Replies
6
Views
285
Replies
3
Views
80
Replies
2
Views
122

1,186,326
Messages
5,957,228
Members
438,294
Latest member
Soikeo79

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back