CountIfs and Blanks, not blanks

Barry Walls

Hi Guys

 

I have two columns of data that I want to summarise with a count into a specific cell where the follpowing conditions are true:

 

The first column is NOT blank AND the second column IS Blank.

 

Not sure if CountIf's is right for this. Any help would be appreciated!

 

All the best

 

Barry

Jim Hook

Hi Barry,

 

I think the following will do what you're looking for assuming that the cells of interest are in columns A and B from rows 1 to 10.

 

The COUNTIFS() function can only check for  non-blank specific values as arguments. To get around this, create a column C next to B with the following formula:

 

=IF(AND(NOT(ISBLANK(A1)), B1 = ""), "Y", "")

 

This checks for column A being not blank and B being blank and needs to be entered in cells C1 - C10.

 

Then, using the COUNTIFS() function do the following"

 

=COUNTIFS(C1:C10, "Y") to get the number of times the condions were met.

 

Barry Walls

Thank you for your reply Jim. I'll try that tomorrow and let you know how I get on, one way or another.

 

All the best

 

Barry

 

 

Jim Hook

Just a thought, if you changed the first IF() function to put a 1 instead of a "Y" in the cell, you could use a simple =sum(C1:C10) as the last step to get the number of times the conditions were met.

Barry Walls

Jim, 

 

Thankyou so much! I got the "1"s to display in the colums I needed but for some weird reason, when I use the SUM finction to count the range, the value returned is always zero. The column is properly formatted for text/numbers and the formular is correct 

 

=SUM([Count1]1:[Count1]360) 

 

So not sure if you can shed any light on this?

 

Thanks again for your help. 

 

All the best

 

Barry

Jim Hook

Barry,

 

If you see the 1s but they are not counting, maybe they are text 1s. In the formula that puts 1s in a cell if the conditions are met make sure they don't have quotes around the 1s.

Barry Walls

I got it Jim. I just used count rather than sum and it worked perfectly after I used your "1" suggestion. 

 

I'm now trying to work out how to reference the last cell in a column that has a value and grab the value for part of another formula. I have Xn rows which will change so I've numbered them and want to get the value in the the bottom cell (which counts the number of records). Any ideas?

 

All the best

 

Barry

Jim Hook

Barry, nothing comes to mind on getting the value in the last cell that has a value.