Counting blanks in a Contacts column
I'm trying to count the number of blank cells in a Contacts column in a remote sheet. Here's the formula:
=COUNTIF({SEOnboarding Tracker  DM}, "")
The problem is ... it's returning a value of 30 when it should be 1. There's only 20 rows and only one of them doesn't have a Contact stored in the DM column. And yes I'm 100% sure there aren't any blank rows at the end of my sheet. Someone suggested I use this instead:
=COUNTIF({SEOnboarding Tracker  DM}, "=")
That returns a value of 20 which is the number of rows including the one with the blank. Any suggestions?
Best Answers

Sorry about that. I completely missed that one.
Your latest does make sense though. There are always at a minimum 50 rows in a sheet, and you have 10 filled in.
One last thing to try on the original... Is there another column that will always have something in it on every row that needs to be counted? If so you can count where the contact column is blank and that other column is not blank. There still seems to be some issue with your first sheet considering even when we count nonblanks we get the wrong number, but it is at least one more thing to try that Support will probably also suggest.
=COUNTIFS({Contact Column}, @cell = "", {Other Column}, @cell <> "")

I was not aware of the 50 row internal limit. Good to know. I now see why you had me try 10. If my sheet had more than 50 rows, that would have worked but I’ll be honest, I’m not entirely sure why :P
Anyway, your formula suggestion gives me the output I’m expecting. Thank you Mr. Newcome. You’re the best!
Answers

What happens if you try this:
=COUNTIF({SEOnboarding Tracker  DM}, @cell = "")  10

Hey Paul,
If I add  10 to the formula the value changes to 20. The correct value is 1. I have no clue why this is not working.

I reread the last post, I changed it to:
=COUNTIF({SEOnboarding Tracker  DM}, @cell = "")  10
and the value is now 20 (when it should be 1).

Double check that you selected the correct column for your cross sheet reference.
If that is correct, what happens when you plug in
=COUNTIF({SEOnboarding Tracker  DM}, @cell <> "")

If I change the formula to:
=COUNTIF({SEOnboarding Tracker  DM}, @cell <> "")
The value is 20. The cross sheet reference is correct (see attached). The sheet has 21 rows; only the first row has a blank in the DM column.

So if we count blank cells and subtract the 10 from the bottom of the sheet then we get 20. But if we count nonblanks then we also get 20. Basically it sounds as if each of the cells in rows that are actually being used are being counted as both blank and nonblank at the same exact time.
Are you able to provide a screenshot of the formula actually in the sheet similar to the snippet below?

Revert back to the original formula. I did a mouse over instead so you could see the value it's returning. This seems like a bug IMO. This source sheet only has 21 rows. I've deleted rows at the bottom of the the source sheet several times just to be 100% sure there's aren't any blank rows that are throwing off the count.

And are you able to show the referenced column like so? It does sound like you may be stuck with a bug though.

That screenshot was in the previous post but thank you for trying to help me. I've opened a support case for this. I was able to duplicate using two new sheets and a very simple example (see attached).

Sorry about that. I completely missed that one.
Your latest does make sense though. There are always at a minimum 50 rows in a sheet, and you have 10 filled in.
One last thing to try on the original... Is there another column that will always have something in it on every row that needs to be counted? If so you can count where the contact column is blank and that other column is not blank. There still seems to be some issue with your first sheet considering even when we count nonblanks we get the wrong number, but it is at least one more thing to try that Support will probably also suggest.
=COUNTIFS({Contact Column}, @cell = "", {Other Column}, @cell <> "")

I was not aware of the 50 row internal limit. Good to know. I now see why you had me try 10. If my sheet had more than 50 rows, that would have worked but I’ll be honest, I’m not entirely sure why :P
Anyway, your formula suggestion gives me the output I’m expecting. Thank you Mr. Newcome. You’re the best!

Happy to help. 👍️
Yes. Your sheet will always have 50 rows or your current number of used rows plus 10. That is why the 10 would have worked if you had 40+ rows. If I had caught that piece in your original post, I may have gotten to the resolution a little quicker. Sorry about that.
Help Article Resources
Categories
Check out the Formula Handbook template!