COUNTISF not counting if >< value is = to zero

Options

Hello,

I have a formula that works as expected as long as the >< or ='s value is greater than 0 however, when I adjust the formula to count the number of cells with 0.00's nothing is returned. Respectfully, there are eleven cells containing 0.00. What am I missing here?

Working formula:

=COUNTIFS([Date of Warranty Claim]:[Date of Warranty Claim], >=DATE(2020, 5, 1), [Warranty Remaining]:[Warranty Remaining], >=0.01, [Warranty Remaining]:[Warranty Remaining], <=2)


No results formula:

=COUNTIFS([Date of Warranty Claim]:[Date of Warranty Claim], >=DATE(2020, 5, 1), [Warranty Remaining]:[Warranty Remaining], =0, [Warranty Remaining]:[Warranty Remaining], =0)



Thank you,

Best Answers

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭
    Answer ✓
    Options

    @LeAndre P Any chance there is a fractional value at the nth decimal place? And, are you sure the character in that column is a number (value) not text?

    Cheers,

    Dale

  • LeAndre P
    LeAndre P ✭✭
    Answer ✓
    Options

    Hi Dale,

    thank you. I don't think small residual values are the issue. Also, I tried 0.00000001 and great than -0.00000001 to no avail.

    The formula in the Warranty Remaining column is as follow:

    =IF([Warranty Expiration Date]@row > TODAY(), NETDAYS(TODAY(), [Warranty Expiration Date]@row) / 365, IF([Warranty Expiration Date]@row < TODAY(), "0.00"))

    Could this be where the issue lies? Remember it is the result of the above formula that I am trying to count in the following formula that is not working:

    =COUNTIFS([Date of Warranty Claim]:[Date of Warranty Claim], >=DATE(2020, 5, 1), [Warranty Remaining]:[Warranty Remaining], =0, [Warranty Remaining]:[Warranty Remaining], =0)

  • LeAndre P
    LeAndre P ✭✭
    Answer ✓
    Options

    Hi Dale,

    I managed to get it working by changing the preceding formula to return a blank cell if the NETDAYS conditions were met. Subsequently, I changed the second formula(originally looking to count 0's) to count the number of blank cells instead, which I know are indicators for zero's and it worked.

    Revised preceding formula:

    =IF([Warranty Expiration Date]@row > TODAY(), NETDAYS(TODAY(), [Warranty Expiration Date]@row) / 365, IF([Warranty Expiration Date]@row < TODAY(), ""))

    2nd Revised formula:

    =COUNTIFS([Date of Warranty Claim]:[Date of Warranty Claim], >=DATE(2020, 5, 1), [Warranty Remaining]:[Warranty Remaining], ="", [Warranty Remaining]:[Warranty Remaining], ="")


    Best,

Answers

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭
    Answer ✓
    Options

    @LeAndre P Any chance there is a fractional value at the nth decimal place? And, are you sure the character in that column is a number (value) not text?

    Cheers,

    Dale

  • LeAndre P
    Options

    The column is Text/number formatted. If I understand your question.



  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭
    Options

    Cool. Yes, that was half the question.

    The other half would be focused on the formula that generates the zero. Any chance there is a very small residual value that you can't see?

    (The quick and dirty fix is to look for a value less than 0.00000001 and great than -0.00000001. That gets you pretty close to zero.)

    Cheers,

    Dale

  • LeAndre P
    LeAndre P ✭✭
    Answer ✓
    Options

    Hi Dale,

    thank you. I don't think small residual values are the issue. Also, I tried 0.00000001 and great than -0.00000001 to no avail.

    The formula in the Warranty Remaining column is as follow:

    =IF([Warranty Expiration Date]@row > TODAY(), NETDAYS(TODAY(), [Warranty Expiration Date]@row) / 365, IF([Warranty Expiration Date]@row < TODAY(), "0.00"))

    Could this be where the issue lies? Remember it is the result of the above formula that I am trying to count in the following formula that is not working:

    =COUNTIFS([Date of Warranty Claim]:[Date of Warranty Claim], >=DATE(2020, 5, 1), [Warranty Remaining]:[Warranty Remaining], =0, [Warranty Remaining]:[Warranty Remaining], =0)

  • LeAndre P
    LeAndre P ✭✭
    Answer ✓
    Options

    Hi Dale,

    I managed to get it working by changing the preceding formula to return a blank cell if the NETDAYS conditions were met. Subsequently, I changed the second formula(originally looking to count 0's) to count the number of blank cells instead, which I know are indicators for zero's and it worked.

    Revised preceding formula:

    =IF([Warranty Expiration Date]@row > TODAY(), NETDAYS(TODAY(), [Warranty Expiration Date]@row) / 365, IF([Warranty Expiration Date]@row < TODAY(), ""))

    2nd Revised formula:

    =COUNTIFS([Date of Warranty Claim]:[Date of Warranty Claim], >=DATE(2020, 5, 1), [Warranty Remaining]:[Warranty Remaining], ="", [Warranty Remaining]:[Warranty Remaining], ="")


    Best,

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!