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
 10.4K Get Help
 61 Global Discussions
 46 Industry Talk
 384 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!