Use COUNTIF with AND to add additional count conditions from other columns in same sheet
I want to count the number of times a certain parameter is assigned a certain category.
I have 3 parameters that I track "incidents" for: HG, PM and SO2.
In my summary sheet, I want to count the number of times in the sheet each parameter is assigned the EPA Downtime Category: "Monitoring System Malfunction" for use in a key metric summary on a dashboard.
I have included a picture of the table below - yes, I know it looks like a unicorn barfed🦄 🌈
Below are attempts that I expected to get zero for, but that result in #UPARSEABLE:
=COUNTIF([EPA DT Cat]:[EPA DT Cat], "Monitor Equipment Malfunctions", AND(@row ="HG"))
I also tried:
=COUNTIF([EPA DT Cat]:[EPA DT Cat], "Monitor Equipment Malfunctions", AND([DT Parameter]@row ="HG"))
I can't put a specific name for the parameter in the formula to get a full count since the DT Parameter Name changes with each incident - e.g., U17 HG Incident #1, U17 PM Incident #2, etc.
I tried the CONTAINS function in the example below to get a count of when anytime the category is assigned and there is an HG in the name, but this also got an UNPARSEABLE error:
=COUNTIF([EPA DT Cat]:[EPA DT Cat], "Monitor Equipment Malfunctions", IF(CONTAINS("HG", @row)))
The following attempt got an INCORRECT ARGUMENT SET, which I figure is more hopeful😋:
=COUNTIF(AND([EPA DT Cat]:[EPA DT Cat], "Monitor Equipment Malfunctions", [DT Parameter]:[DT Parameter] = "HG"))
I am terrible at formula logic 😳and try to just fool around with other formulas that are close to one until I get something that sticks, so any help is much appreciated!😀
Molly
Best Answer
-
Taking a closer look, lets start with this...
=COUNTIFS([EPA DT Cat]:[EPA DT Cat], "Monitor Equipment Malfunctions", [DT Parameter]:[DT Parameter], CONTAINS("HG", @cell))
Answers
-
I'll have to read your post again to see if there is anything else, but right off I notice that you are probably going to want to use a COUNTIFS to incorporate multiple range/criteria sets.
-
Taking a closer look, lets start with this...
=COUNTIFS([EPA DT Cat]:[EPA DT Cat], "Monitor Equipment Malfunctions", [DT Parameter]:[DT Parameter], CONTAINS("HG", @cell))
-
Worked perfectly - Thank you so much! I knew it was probably something simple! 🤩
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!