Using COUNTIF to find the number of Blank Spaces

cdvl

I'm trying to count how many blank spaces there are in a given column. However, whenever I put in the below formula, it just gives me the total amount of rows in the column, but the entire column is not blank:

=COUNTIFS({CDVL Project Tasklist Range 4}, "")

I've also tried this formula, but to the same results:

=COUNTIFS({CDVL Project Tasklist Range 4}, ISBLANK(@cell))

What am I missing? 

EDIT: I've included a screenshot of the data with names blacked out. As you can see, what I have labeled at CDVL Project Tasklist Range 4 does have data in it. Some previous formulas I've used for the range include =COUNTIFS({CDVL Project Tasklist Range 4}, "Blue"), =COUNTIFS({CDVL Project Tasklist Range 4}, "Yellow"), etc. corresponding to the status color, and they've all worked. I have no clue way the BLANK formula is not working. 

Paul Newcome

Are you able to post some screenshots with dummy data? Based on your formula and explanation, this should be working for you...

Debbie Sawyer

Those formulas work for me too - Are you absolutely certain that Range 4 is not a blank column?

I'd too be interested in screen shots to understand why this isn't working for you...

cdvl

Just posted a screenshot! 

cdvl

Range 4 is not a blank column. I've used other formulas on it such as =COUNTIFS({CDVL Project Tasklist Range 4}, "Yellow") and =COUNTIFS({CDVL Project Tasklist Range 4}, "Blue") and both worked. I posted a screenshot on my original post. I'm not sure why it isn't working for me either. 

Paul Newcome

Is there a formula in the empty cells?

Paul Newcome

Is there data in the Primary Column for the empty rows?

Debbie Sawyer

Good call!  Is the Ball being selected from an IF function? or are the Blue and Yellow balls being selected manually?  Yes - good question Paul.

cdvl

The Balls are being selected manually from a drop down list for the column. 

cdvl

There's no formula in the empty cells. 

Paul Newcome

I keep thinking of this in the wrong way... The ISBLANK is not skipping blank cells. It is including non-blank cells in the count...

 

Hmm............. 

 

Try that same formula in two more places...

1. A different range on the same sheet.

2. A range on a completely different sheet.

Debbie Sawyer

When setting your range in the cross reference {CDVL Project Tasklist Range 4} (where you have taken your screen shot) are you clicking on the Status of Task heading to select the whole column or are you selecting a range of rows within that column?

 

Debbie Sawyer

Am really keen to help you get to the bottom of this - all my tests at reproducing it are failing to get the wrong answer! It all just keeps working...

my email address is:

debbie.sawyer@smarterbusinessprocesses.com

I would like to send a zoom meeting invite to you, so we can screen share and you can show me the sheet and the results, then I can maybe talk through some suggestions.  This way you won't be sharing a sheet with me, but we might, potentially see where the error is.

If you would like to do this please do respond and we can set up a good time!  I am based in the UK, but will log in at any time I can to accommodate this one...!

cdvl

I'm clicking on the Status of Task heading to select the whole column. 

cdvl

I tried the same formula with a different range on the same sheet and had the same odd results.

cdvl

Thank you for the offer! I think I figured it out though.

My solution the sheet has been updated since I last was here and if it was just counting the total cells, blank or non-blank, it would be 68. However, the total remains at 66 for the ISBLANK formula. The ISBLANK formula should result in the number 56. I found this number by adding up all of the filled columns by using a COUNTIFS={...Range 4}, <> "" and double checked this number by manually counting the filled columns in the range.

I then noticed a pattern. Each place where I tried the same formula, the result was off by 10. Why? I discovered that at the bottom of the sheet are ten blank rows! I tried deleting them, but Smartsheet won't let me, so I just made the following formula with an additional range referring to the task title: COUNTIFS=({...Range 4, ISBLANK(@cell), {...Range 5}, <> "").  Now my totals are accurate!