Help on formula: count if with multiple criteria

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

Jessica Zahner

@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

@Jessica Zahner Which range is the "Air-Evac Lifeteam" referring to?

Jessica Zahner

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

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

Jessica Zahner

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

Thank you again!

Paul Newcome

@Jessica Zahner Happy to help. 👍️

Kim Goldsmith

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

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

Amir Furhang

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å

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!

Josh ciaramitaro

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

@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

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

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