Help on formula: count if with multiple criteria

1356

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️


    And yes. you will just type "@cell". It is case sensitive as well, so make sure it is all lower case. Also yes. It is basically telling the formula to evaluate the previously established range on a cell by cell basis. It allows you to write the "logical statement" portion of "this equals that".

  • @Paul Newcome as I am creating this sheet further I realize I want to add another condition, I have been trying to add it multiple ways using OR/AND/COUNTIF

    I would like it to have 3 different specifications, 1 for the active, active/plan refurb/refurb (which we do), 2 for the specific make (which we do), 3 for the subsidiary (been trying to get)

    Tried a few different things and placements, but here was me trying to use AND

    =COUNTIFS({Aircraft Status}, OR(@cell = "Active", @cell = "Active/Plan Refurb", @cell = "Refurb"), {Make}, Make@row), AND (@cell,= "Air-Evac Lifeteam))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
  • @Paul Newcome It is from same sheet that I am pulling the active #s just different column.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. You need to specify which column.


    =COUNTIFS({Aircraft Status}, OR(@cell = "Active", @cell = "Active/Plan Refurb", @cell = "Refurb"), {Make}, Make@row, {NEED RANGE HERE}, @cell = "Air-Evac Lifeteam")

  • I have been working on this for so long, I don't think my brain is working. That makes complete sense.

    Thank you again!

  • This is similar to a problem I'm working on. Could you help me to use COUNTIF the column CONTAINS one of two different words?

    The problem I am running into is I don't want to count the cell twice if it contains both words.

    Thanks in advance!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Kim Goldsmith Using the method above with the OR function, it should not count twice if a cell contains both. It isn't scanning the range looking for "Word 1" and then scanning the range a second time looking for "Word 2" and adding those two together. It is scanning the range once and counting the cells once that have either "Word 1" or "Word 2".

  • All I've never posted to this forum before. Excellent information. Hoping someone can take me one step farther than the questions already posted. I need a countif statement with multiple criteria but need to include a date feature. I've seen questions and examples using specific dates but what if I want all cells in a certain range that are less than 30 days old for example? Can anyone provide an example formula for that?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Amir Furhang

    I hope you're well and safe!

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Josh ciaramitaro
    edited 09/20/21

    Sorry to piggyback off this request, but I tried using the formula pasted here from Paul Newcome


    =COUNTIFS(Color:Color, OR(@cell = "red", @cell = "blue"))


    And could not get it to work with the following setup:

    =COUNTIFS({InfoSec PPL and Tracker Range 2}, <TODAY(), OR ({InfoSec PPL and Tracker Range 1} <> "Complete", {InfoSec PPL and Tracker Range 1} <> "Discarded"))


    I'm trying to display entries that are less than today and not equal to a complete or discarded status. Any help is greatly appreciated!


    **Update


    Overthought the process here is the simple string:

    =COUNTIFS({InfoSec PPL and Tracker Range 2}, <TODAY(), {InfoSec PPL and Tracker Range 1}, <>"Complete", {InfoSec PPL and Tracker Range 1}, <>"Discarded")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Josh ciaramitaro Yes. You definitely want to go that route (or use the AND function). The OR function when used with the proper syntax still would not have worked because technically "Complete" is not "Discarded" and the other way around. That means they would both have ended up being counted.


    Glad you got it figured out.

  • SHOOD
    SHOOD ✭✭✭✭

    Hello!

    I am fairly new to Smartsheets and am trying to create a Sheet Summary to use on a Dashboard

     

    I have a filter on the Smartsheet that returns 172 rows (ID Type=Unit Test, In Scope checked, Workset=Workset A

    I am trying to do a Sheet Summary

    Total Scenarios Unit Testing, In Scope, in Workset A


    I have tried the following:

    =COUNTIFS([ID Type]:[ID Type], "Unit Test", [In Scope]:[In Scope], 1, Workset:Workset, CONTAINS("Workset A", @cell)) #NO MATCH

     

    =COUNTIFS([ID Type]:[ID Type], "Unit Test", [In Scope]:[In Scope], 1, Workset:Workset, CONTAINS("Workset A")) #NO MATCH

     

    =COUNTIFS([ID Type]:[ID Type], "Unit Test", [In Scope]:[In Scope], 1, Workset:Workset, "Workset A") #NO MATCH

    It seems to be an issue with the Workset column because the following does work:

    =COUNTIFS([ID Type]:[ID Type], "Unit Test", [In Scope]:[In Scope], 1, Status:Status, "Pass/Complete")

    The Workset column was a multi select drop down. I changed it to single select drop down to see if that made a difference but it didn’t.

    Any ideas on why this isn’t working?

    Thanks!!!!

    Susan

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @SHOOD Is that error present in any cell in the Workset column?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!