EXCEL: Can I get a count of a certain value from columns with certain headings, only from the columns with certain text?

Question

A screenshot of the spreadsheet1

Hi I am looking for a way (in the form of an Excel formula) that can do this

I need to count the number of "o" in columns with headings "Threat", for each row. For example, I need counts of "o"s in row 2, 4, 6, and so on, but only the "o"s in columns with heading "Threat" in row 1.

BUT it gets complicated, I need more. I need to distinguish the counts of "o"s, depending on if the cell beneath the "o" in columns "Threat" has the text "P" or "A".

So I need to find the way to count "o"s in row 2, only in columns with the heading "Threat", and only the "o"s with "P" in the cell underneath the "o".

Any advice will be greatly appreciated!

Edit: Come to think of it, since the "P"s and "A"s are in the same columns as "Threat", the heading can be taken out of the equation! So just need to find a formula that would help me count the number of a certain value in row 2, that are only in the column that has "P" in row 3.

Answer 1

Try

=SUMPRODUCT(($A$2:$E$10="o")*($A$3:$E$11="P"))

Adjust the ranges to suit your sheet. Do not use with whole column references, unless you like staring at the "Calculating ..." message. If the spreadsheet grows, consider using dynamic ranges.