Question

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 · Accepted Answer

@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 · Accepted Answer

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 · Accepted Answer

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,