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!