COUNTIFS isn't working

Hi

I am trying to count all the number of 0s within a column. I am using this formula

=COUNTIFS([Warranty Remaining]:[Warranty Remaining], 0)

but it does not work. What is weird is that, I changed the formula from [Warranty Remaining] to count a specific number in the [Warranty Obligations] column and it worked fine. I've even changed the name of the column the formula is in and I only get 0 instead of the accurate 0s in the column. The column has many 0,1,2,3,4,5,6 and I just want to count the 0s.

Thanks in advance


Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Potentially a silly question, but are the zero's really zero's and not 0.000123 as an example?

    Would the formula work if it was looking for numbers less than 1 ?

    Hope this helps and that you have a great day,

    Jason

  • Yes the 0s are 0s however, the following formula is in the column providing the data for the COUNTIFS column.

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

    I don't believe this is an issue because both formulas are playing well with each other in two other columns.

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    So, I'm wondering if the inverted commas around the 0 is converting the number to a text?

    What happens if

    • your count ifs formula looks for "0" instead of 0 ? or
    • your Warranty Expiration Date inserts a 0 instead of "0" ?

    Hope this helps and that you have a great day,

    Jason

  • Hi Jason,

    I've tried the formula both ways, both with and without the commas to no avail.

  • I got it working. It turns out is was the commas in the formula supplying the data causing the problem.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!