COUNTISF not counting if >< value is = to zero

LeAndre P

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,

Dale Murphy

@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

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

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,

Dale Murphy

@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

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

Dale Murphy

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

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

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,