COUNTIFS unable to acquire accurate return count

bsmith4

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

Paul Newcome

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 = ""))

Paul Newcome

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 = ""))

bsmith4

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!

Paul Newcome

Happy to help. 👍️