Countifs With Multiple Criteria

Options


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

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    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
    otavilog ✭✭
    edited 09/05/23
    Options

    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
    Nick Korna ✭✭✭✭✭✭
    Options

    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
    Options

    @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.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    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
    otavilog ✭✭
    edited 09/05/23
    Options

    @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
    Nick Korna ✭✭✭✭✭✭
    Options

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

  • LesahDee
    Options

    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"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!