=COUNTIFS NEED HELP

Options
LesahDee
LesahDee ✭✭
edited 10/25/23 in Formulas and Functions

Help


I need help with this formula..... I want to count if it is facilities and when the other range is not Expired no extension and not closed.

=COUNTIFS(AND({Munis Range 4},"FACILITIES",{Munis Range 3},<> “EXPIRED NO EXTENSION”,{Munis Range 3},<> “CLOSED” )

=COUNTIF({Munis Range 4},"FACILITIES",{Munis Range 3},<> “EXPIRED NO EXTENSION”,{Munis Range 3},<> “CLOSED” )


I get unpareeable or invalid argument depending on which one I use

Answers

  • Scott Peters
    Scott Peters ✭✭✭✭✭✭
    Options

    Hi @LesahDee - Have you tried simplifying your formula just long enough to troubleshoot? COUNTIFS expects all of the critieria to be true at the same time, so depending on your data, it might work better if you try:

    =COUNTIFS({Munis Range 4},"FACILITIES",{Munis Range 3},<> “EXPIRED NO EXTENSION”)+COUNTIFS({Munis Range 4},"FACILITIES", {Munis Range 3},<> “CLOSED”)

    I recommend trying each half of that as a standalone formula to see if there is a spelling or reference problem first, and then bring them together if each works on their own.

  • Matthew J McAteer
    Matthew J McAteer ✭✭✭✭
    edited 10/25/23
    Options

    Hi LesahDee,

    Try =COUNTIFS({Munis Range 4},"FACILITIES",{Munis Range 3}, HAS(@cell, " ", @cell <> “EXPIRED NO EXTENSION”, @cell <> "CLOSED"))

    Matthew

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    edited 10/25/23
    Options

    =COUNTIFS({Munis Range 4},"FACILITIES", {Munis Range 3}, AND(@cell <> “EXPIRED NO EXTENSION”, @cell <> “CLOSED”

  • LesahDee
    Options

    So none of them worked, however I found a video and we were all so close


    =COUNTIFS({Munis Range 4}, Department@row, {Munis Range 3}, <>"CLOSED", {Munis Range 3}, <>"DO NOT RENEW")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!