COUNTIFS getting Invalid REF

I am using this formula but getting invalid ref.

=COUNTIFS({InforWMS Upgrade Myerstown UAT Range 24}, "", {InforWMS Upgrade Myerstown UAT Range 24,"N/A"})

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Rodney Doucett

    Looks like you've got a misplaced end curly bracket } here:

    {InforWMS Upgrade Myerstown UAT Range 24,"N/A"})

    But also, even it were placed correctly, you wouldn't get a count of anything. What this formula is asking is "Count all the rows in this range that equal both blank and "N/A"." A cell can't be blank and also be some other value, and both criteria have to be true in order for the row to be counted. I think what you're looking for is an OR statement in there, to count all the rows that are either blank or "N/A". Try this:

    =COUNTIFS({InforWMS Upgrade Myerstown UAT Range 24}, OR(ISBLANK(@cell), @cell = "N/A"))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Rodney Doucett

    Looks like you've got a misplaced end curly bracket } here:

    {InforWMS Upgrade Myerstown UAT Range 24,"N/A"})

    But also, even it were placed correctly, you wouldn't get a count of anything. What this formula is asking is "Count all the rows in this range that equal both blank and "N/A"." A cell can't be blank and also be some other value, and both criteria have to be true in order for the row to be counted. I think what you're looking for is an OR statement in there, to count all the rows that are either blank or "N/A". Try this:

    =COUNTIFS({InforWMS Upgrade Myerstown UAT Range 24}, OR(ISBLANK(@cell), @cell = "N/A"))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • perfect. thanks that worked

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!