Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Countifs With Multiple Criteria

    ✭✭✭✭
    in Formulas and Functions


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

    image.png


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

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • Community Champion

      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! 🙂

      · Share on FacebookShare on Twitter
    • ✭✭✭✭
      edited 09/05/23

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

      · Share on FacebookShare on Twitter
    • Community Champion

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

      image.png

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭


      · Share on FacebookShare on Twitter
    • Community Champion

      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!

      · Share on FacebookShare on Twitter
    • ✭✭✭✭
      edited 09/05/23

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

      · Share on FacebookShare on Twitter
    • Community Champion

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

      · Share on FacebookShare on Twitter
    • ✭✭

      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"

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions

    • Formula Help

      I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
      User: "Allan Z"
      Answered ✓
      Formulas and Functions
      8
      2

    • Unparseable Error

      Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
      User: "stratman"
      Answered ✓
      Formulas and Functions
      15
      2

    • Formula to return the non-blank cell

      I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …
      User: "m_anderson"
      Answered ✓
      Formulas and Functions
      13
      2