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.



Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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 = ""))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!