COUNTIFS unable to acquire accurate return count
Hi, I am attempting to use the formula below and the criteria is if the cell has a value but it is not 100% then add it to the invalid count, the other criteria is if the cell is blank then add it to the invalid count.
1) {InfraSecurityRangeProjectCode} is a range comprising a column which is essentially the primary column so I am testing this to ensure it is not blank. This returns 34 rows which is the extent of my data. I've read other articles and this guarantees I will not be including blank rows beyond the end of the data.
2) I need using another range shown below as {InfraSecRangeResource} to see if the value in the cell is less than 100% (aka <1) or if it is blank. There are 34 rows in my data and the below formula returns 0.
=COUNTIFS({InfraSecurityRangeProjectCode}, NOT(ISBLANK(@cell)), {InfraSecRangeResource}, <1, {InfraSecRangeResource}, ISBLANK(@cell))
Any advice would be much appreciated.
Regards,
Brian
Best Answer

That is because when you use the basic syntax of COUNTIFS it is an implied AND. So basically your last to range/criteria sets are saying to count if the range is blank AND the range is less than 1. You would want to incorporate an OR function in like so...
=COUNTIFS({InfraSecurityRangeProjectCode}, @cell <> "", {InfraSecRangeResource}, OR(@cell < 1, @cell = ""))
Answers

That is because when you use the basic syntax of COUNTIFS it is an implied AND. So basically your last to range/criteria sets are saying to count if the range is blank AND the range is less than 1. You would want to incorporate an OR function in like so...
=COUNTIFS({InfraSecurityRangeProjectCode}, @cell <> "", {InfraSecRangeResource}, OR(@cell < 1, @cell = ""))

Oh wow, I didn't know you could put an "OR" in for the value. This is excellent and it worked perfectly. I can use this sort of syntax elsewhere as well. Thanks so much Paul!

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!