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


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=559314

countif(A3:BK3;isblank())

Vahur


--
wahur
------------------------------------------------------------------------
wahur's Profile: http://www.excelforum.com/member.php...o&userid=26555
View this thread: http://www.excelforum.com/showthread...hreadid=559314

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, say
A1:F10 and I wanted to find out how many "PP Upload"s there were, I'd
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 all
the "PP Upload"s (for example), and then look in the row below and
COUNTIF that cell is blank.

Hope that is clearer.

Sam


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=559314

Hi,

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

With best regards,
Nika Lampe

"wahur" wrote:


countif(A3:BK3;isblank())

Vahur


--
wahur
------------------------------------------------------------------------
wahur's Profile: http://www.excelforum.com/member.php...o&userid=26555
View this thread: http://www.excelforum.com/showthread...hreadid=559314

Nika,

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

Sam


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=559314

Try...

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

Hope this helps!

In article ,
SamuelT wrote:

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, say
A1:F10 and I wanted to find out how many "PP Upload"s there were, I'd
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 all
the "PP Upload"s (for example), and then look in the row below and
COUNTIF that cell is blank.

Hope that is clearer.

Sam

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


--
blissworks
------------------------------------------------------------------------
blissworks's Profile: http://www.excelforum.com/member.php...o&userid=36156
View this thread: http://www.excelforum.com/showthread...hreadid=559314