COUNTIF & 'not equal to'

Options

Is there a way to use the COUNTIF function and have a cell counted only if it's NOT equal to a specific value?

I'm tracking a weekly metric. I have several rows with a formula that calculates the % increase of the current week's value vs. the prior week. Since there aren't any values in the future weeks yet, the formula comes back with ""#DIVIDE BY ZERO" . I'd like to use the COUNTIF function to only count rows that have a my metric value in it, but not the rows with ""#DIVIDE BY ZERO" .

Right now, I have just the COUNT function in there, which is counting the error cells (for a total of 51). But I'm aiming to get the "# of weeks completed" row to count only 4 (cells showing n/a, 73.46%, 5%, and -100%). And then when I fill in the next row next week, it would count 5 cells, and so on.












Thanks for any help!

Kate

Answers

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭
    Options

    Hi There,


    You could use either

    COUNTIF(range,<>"#DIVIDE BY ZERO")

    or

    COUNTIF(range, NOT("#DIVIDE BY ZERO")

    You could also use the ISNUMBER FUNCTION

    COUNTIF(ISNUMBER(range))


    Not sure about the syntax on that last one!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!