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({SE-Onboarding 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({SE-Onboarding 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 non-blanks 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({SE-Onboarding 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 re-read the last post, I changed it to:
=COUNTIF({SE-Onboarding 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({SE-Onboarding Tracker - DM}, @cell <> "")
-
If I change the formula to:
=COUNTIF({SE-Onboarding 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 non-blanks 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 non-blank 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 non-blanks 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!
