Formula not counting correctly
Hi Guys
Please note the formula. see pic1
I am trying to count BS Numbers excluding zeros and blanks
Pic1
However it's not returning the correct count, for some reason the formula is not counting some of the BS Numbers, see pic 2, the ones i circled in blue (see pic2), for formula is not counting them, i want the formula to count all BS number excluding zero and blanks. Please advise
Pic2
Answers
-
That is because you have mixed data types (some rows are numbers and some rows are text). This will always throw things off. You are going to need to insert a text/number column (can be hidden after setting up) and use a formula to convert every row into text (add quote quote).
=[BS Number]@row + ""
Then you can reference this helper column in your COUNTIFS.
-
Hi Paul. Thanx for the advice
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!