# Using countblank within a countifs function



## lisabandit (Feb 14, 2010)

I have two columns with data (A & B). I want to use countifs to count a certain condition that occurs in column A and see if that conditoin corresponds to a blank in that row for column B. For example

_____A_________B

1 __Sam ______Joe

2 __Sam__________

3 __Harry_____ Sue

4__ Harry_________ 

5 __Harry_____ Pete

I would like to get a count for each occurence where both Harry occurs in column A and when there is a blank in column B (across the rows). I tried =countifs(A1:A5,"=harry", B1:B5,"=countblank") hoping to get the answer 1 (Where Harry + a blank cell are in only one row (4)) with no success.

I know if I want to count when both Harry occurs in A and Sue occurs in B it's =countifs(A1:A5,"=Harry", B1:B5,"=Sue") and I get the answer 1 (where Harry + Sue occur in one row (3)). But I can't seem to apply the same logic with countblank in the countifs function. Please help Thanks.

PS. The real data set is about 500 columns, this is just an example.


----------



## slurpee55 (Oct 20, 2004)

This formula should do it:
=SUMPRODUCT(--(A1:A5="Harry"),(B1:B5=""))
(it works with a slight change in Open Office [OO uses semi-colons instead of commas] - in Excel you may need to do it as:
=SUMPRODUCT(--(A1:A5,="Harry"),(B1:B5,="")) - I am not sure.)
Or you could do it very simply in two formulas - just concantenate the data in A and B using this in C and dragging down:
=(A1&B1)
and then use this formula:
=COUNTIF(C1:C5,"Harry") (or, to find two names, it would be =COUNTIF(C1:C5,"HarrySue")


----------



## lisabandit (Feb 14, 2010)

Perfect! Thank you.


----------



## slurpee55 (Oct 20, 2004)

Oh, a belated welcome to the forum!
Glad to help. 
Please use the button at the top left of the page to mark this thread as Solved.
See you around!


----------

