Use COUNTIF with AND to add additional count conditions from other columns in same sheet

Molly Long

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

Paul Newcome

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

Paul Newcome

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.

Paul Newcome

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

Molly Long

Worked perfectly - Thank you so much! I knew it was probably something simple! 🤩

Paul Newcome

Happy to help. 👍️