countifs statement with multiple criteria for multiple criteria ranges

Hi, all,

I have 3 columns of data: Unit (Column A), Prob (B), Sever (C). By unit, I want to count the # of cases where (Prob = "HIGH" or "MEDIUM") and (Sever = "HIGH" or "MEDIUM").

I have attached a sample workbook to illustrate my problem. Column N includes my try at the correct formula, but as you will see it is not working.

The formula I am trying is:
=SUM(COUNTIFS($A$2:$A$61,"U"&M7,$B$2:$B$61,{"MEDIUM","HIGH"},$C$2:$C$61,{"MEDIUM","HIGH"}))

I notice if I change the order of "MEDIUM" and "HIGH" within the curly brackets that it changes the results.

Let me know what additional information will help clarify my intent. Thanks for the help!

test.xlsx

That almost works. For U120, the count is 5 although in column C "SEVER" is listed as LOW for one instance, so the count should be 4. I need the count to only occur if (prob=high or medium) AND (sever=high or medium)


Capture.JPG

Regarding the result list in N column,

Why you don't create copy unique filter? notice that some cells has double data ( Example : 113 ), IMHO it will causing biased when you sum the data.

That works! Although, the reason I am looking for a formula with multiple criteria for multiple criteria ranges is that I have other similar tasks that involve summing that can't be simplified into a single criteria. For instance, I will need to perform a count if (prob=H and sever=L) or (prob=L and sever=H) or (prob=M and sever=M). Perhaps I can get something to work, but if you have any insight it would be appreciated!

Great, Thanks for the help!