Counting Blank Cells

**Larko** · 04-27-2010, 06:16 AM

Hi All,
I am trying to create a formula that counts blank cells in the next 7 cells in the row, however only counting up to the first non blank cell, eg -

blank blank blank 1 1 blank blank

Is currently returning 5, and I want it to return 3. I am currently using an if statement and countblank.

Any help would be appreciated.
Thanks
Richard.

**DonkeyOte** · 04-27-2010, 06:20 AM

It would help if we had some ranges with which to reference and understood the non-blank data type consistency (always number, text etc)...
Also are the blanks Nulls (formula) or true blanks ? (use of COUNTBLANK implies Nulls in use)

Let's assume your values are in row 1

=MATCH(TRUE,INDEX(1:1<>"",0),0)-1

(where no non-blank/nulls exist an error will be returned - handle as nec.)

If the formula is to reside in the same row as the values then adjust the ranges as nec. (ie to avoid circular references).

**Larko** · 04-27-2010, 06:27 AM

=IF(Avaliability!F8=1,"",COUNTBLANK(Avaliability!G8:M8))

Is the current formula. The blanks are completely empty cells, the populated cells are all 100%.

Thanks
Richard.

**DonkeyOte** · 04-27-2010, 06:35 AM

So to be clear - the 100% / blank range is Availability!F8:M8 and you're checking F8 first for obvious reasons (ie if 1 then result must be 0), is that correct ?

If so, perhaps:

=IF(Availability!F8=1,"",IF(COUNT(Availability!F8:M8),MATCH(1,Availability!F8:M8,0)-1,8))

**Larko** · 04-27-2010, 06:49 AM

Yes, if the cell it is checking is 100% then I want a blank cell, if F8 is empty then i want it to go to G8 to see if that is empty, if so then it would go to H8, if that cell is populated then it would return 2. As there are two blank cells before a 100%

**DonkeyOte** · 04-27-2010, 06:57 AM

Have you tested the suggestion ?

**Larko** · 04-27-2010, 07:18 AM

Yes, it returned 8. Not sure why?

**DonkeyOte** · 04-27-2010, 07:45 AM

If it's returning 8 then the implication is that there are no numbers in the range specified (ie COUNT of range returns 0) - are the 1's numbers or numbers stored as text ?

**Larko** · 04-27-2010, 07:56 AM

I see, so if I want to change that to 7 if all cells are empty then I just update the end of the formula? Excellent, thanks very much for your help.

**DonkeyOte** · 04-27-2010, 08:04 AM

Yes.

That was one of the reasons I wanted to know the purpose of Availability!F8
ie that cell formed one of the 100%/nothing cells (as implied by your initial formula) then it followed that if F8:M8 were all blank (ie COUNT = 0) then there were 8 blanks in total and that should be the result