COUNTIFS formula excluding specific text values

[Jnehra]

Hello All

I am having some trouble getting this formula to work the way I'd like. The result I am trying to return is the number of incidents by priority (Column F), while excluding certain text values from 'Resolution notes' column (Column E) from the January Source Data sheet.

I'd like the result to be displayed in column G2-G5 on the All Regions Source Data sheet.

The two values I'd like to exclude in this count are "Duplicate Incident Opened, Do Not Report" and "Incident Opened In Error".

The formula I have tried is =COUNTIFS('January Source Data'!$F$2:$F$10000, F2,"<>Duplicate Incident Opened, Do Not Report"), but it is returning an error.

I have attached the sheet below -- any help is much appreciated!

Thanks in advance...

[Bo_Ry]

Please try at G2 and drag down

=COUNTIFS('January Source Data'!$F$2:$F$10000,F2,'January Source Data'!$E$2:$E$10000,"<>*Incident*")

[KOKOSEK]

=COUNTIFS(criteria_range,criteria,criteria_range2, criteria2....)

Your criteria_range2 = "<>Duplicate Incident Opened, Do Not Report" so it can't works.

[Jnehra]

Bo_Ry

This formula worked, thank you. Can you help me understand the logic behind the formula?

Thanks..

[Bo_Ry]

=COUNTIFS(criteria_range1, criteria 1, [criteria_range2, criteria 2]…)

=COUNTIFS('January Source Data'!$F$2:$F$10000,F2,'January Source Data'!$E$2:$E$10000,"<>*Incident*")


criteria 1 is F2 that is normally use.


You don't want to include both sentences below, and both share the same word "Incident"
and this word does not show for other sentences in 'January Source Data' column F

"Duplicate Incident Opened, Do Not Report" and "Incident Opened In Error"

criteria 2 is "<>*Incident*" * is wildcard can be anything before or behind "Incident"