Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
CountIfs and Blanks, not blanks
Comments

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 nonblank 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.

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

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.

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

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.

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

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