COUNTIF In The Row Below

[SamuelT]

Hi all,

I have a long headers row (A2:BK2) and in the row below there are dates
corresponding to the above. Some of the second row (A3:BK3) does not
have data in various cells and what I want to do is count these empty
cells.

The headers row contains a number of repititions (NB: I didn't create
this spreadsheet - it's horrible!), so in theory I should be able to
use COUNTIF to find out which cells are empty.

My problem is I don't know how to get Excel to first do a COUNTIF on
the header row, and then only COUNTIF there is no data in the row
below.

I hope that makes sense. Any suggestions?

TIA,

SamuelT

 

[wahur]

countif(A3:BK3;isblank())

Vahu

 

[SamuelT]

Thanks Vahur.

To clarify. In the header row I have things such as "Sign-off Date"
"PP Upload" etc repeated a number of times. If I had a range, sa
A1:F10 and I wanted to find out how many "PP Upload"s there were, I'
do:

=COUNTIF(A1:F10, "PP Upload")

If I wanted to count how many blank cells there were, I'd do:

=COUNTIF(A1:F10, "")

What I'm trying to do, however is, in my two rows, is first find al
the "PP Upload"s (for example), and then look in the row below an
COUNTIF that cell is blank.

Hope that is clearer.

Sa

 

[Guest]

Hi,

you can also use formula =Countblank(a3:bk3) to count blank cells in range
from A3 to BK3.

With best regards,
Nika Lampe

 

[SamuelT]

Nika

Thanks for that. Further clarification: I'm not trying just to coun
all the blank cells as a whole, but only those that occur below
certain (e.g. ""PP Upload") header.

Sa

 

[Domenic]

Try...

=SUMPRODUCT(--(A2:BK2="PP Upload"),--(A3:BK3=""))

Hope this helps!

 

[blissworks]

Hi Vahur
Its an amazing solution. I never know that we use something like --
within sumproduct function. Now samuel has got what he wanted.
Quite amazing! are you an Indian?
Cheers
Raj