=COUNTIFS NEED HELP

LesahDee

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

Scott Peters

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

Hi LesahDee,

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

Matthew

Nic Larsen

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

LesahDee

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")