# =COUNTIFS NEED HELP

Options
✭✭
edited 10/25/23

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

• ✭✭✭✭✭✭
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.

• 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

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

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

• ✭✭
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!