Countifs With Multiple Criteria

otavilog

I am trying to count the totals if a certain criterias are met. Per example below I am trying to count if client is A and B where "Initiative" is NOT "N/A" and Progress for both "Waiting for Pricing".

I tried the formula below and not correct syntax and logic. Any help would be much appreciated.

=IF(COUNTIFS({Client}, ="A, {Initiative}, <> "N/A", {Progress}, = "Waiting for Pricing"), COUNTIFS({Client}, ="B", {Initiative}, <> "N/A", {Progress}, ="Waiting for Pricing"))

Nick Korna

Hi @otavilog,

This should do what you're after:

=COUNTIFS(Client:Client, OR(@cell = "A", @cell = "B"), Initiative:Initiative, <>"N/A", Progress:Progress, "Waiting for Pricing")

Hope this helps; if you've any problems or questions then just ask! 🙂

otavilog

Thank you. I tried but since I am referencing an outside sheet. I dont think I got the syntax correct. Could you please advise?

=COUNTIFS({Client}:{ Client}, OR(@cell = "A", @cell = "B"), {Initiative}:{Initiative}, <>"N/A", {Progress}:{Progress}, "Waiting for Pricing")

Nick Korna

For the cross sheet references, if your ranges are single columns then you can use { } round them, you don't need the range (e.g. A:A) like if they are were a column reference.

E.g. your client reference should be like this (you would need to rename it if you wanted it as that, though the formula will work fine with the default inserted instead):

So in this case:

=COUNTIFS({Client}, OR(@cell = "A", @cell = "B"), {Initiative}, <>"N/A", {Progress}, "Waiting for Pricing")

Hope this makes sense - if something is still not clear let me know.

otavilog

@Nick Korna thank you again. One last question (i hope).

How would the syntax be if Client column cells also on an external sheet (A, B and C).

I tried following with no luck:

=COUNTIFS({Client}, = "A", OR({Client}, ="B")

and

=COUNTIFS({Client}, OR(@cell = "A", @cell = "B")

and

=COUNTIFS({Client}, = "A", OR( = "B")

Thank you in advance.

otavilog

Nick Korna

If you're just wanting a count of how many times clients A or B appear on a sheet, then it would be:

=COUNTIF({Client}, OR(@cell = "A", @cell = "B"))

You can add in extra ones by just extending the OR portion of the statement. For example, to add in "C":

=COUNTIF({Client}, OR(@cell = "A", @cell = "B", @cell = "C"))

I'm not 100% sure if this was quite what you mean, so apologies if I have misunderstood - if it isn't let me know!

otavilog

@Nick Korna

Its my fault actually in my previous ask. I only posted half of the formula.

My original ask and the example table I shared are in an external sheet.

My current ask is how to make the formula work based on all the information being in an external sheet. Does this make sense (my apologies).

Regardless, I got it to work because of your input and guidance. Thank you so much.

Nick Korna

Happy to help, if there is anything else just ask! 😊

LesahDee

I am cross referencing 2 different sheets getting an error can someone help.

=COUNTIF({Munis Range 1}, "Facilities", {Contract Extensions Range 1}, NOT("Expired", "Closed"))

I want it to count if the first range is "Facilities" and if the second range is NOT "expired" or "Closed"