COUNTIFS Function for Multiple Criteria in Single & Multiple Column

[petes]

I have 2 columns in spreadsheet as mentioned below:

Column A Column B

Closed Master
Open Slave
Error Master
Hold Master
Reopen Slave

I need to Count all "Closed", "Open" & "Hold" status from Column A and corresponding "Master" status from Column B so that the output of that formula will be "2" as per the above example.

Thanks Guys!!!!

 

[Giordano Bruno]

Nice formula Aladin,

Can you tell me if it is possible to use such a formula and rely upon the array "Closed","Open,"Hold" being entered in a separate single cell rather than being located within the formula?

 

[Aladin Akyurek]

Nice formula Aladin,

Can you tell me if it is possible to use such a formula and rely upon the array "Closed","Open,"Hold" being entered in a separate single cell rather than being located within the formula?

If Closed, Open, and Hold are in a 3-cell area like C2:C4...

=SUMPRODUCT(COUNTIFS(A2:A6,"<>"&C2:C4,B2:B6,"Master")

If the items are in a single cell, say in E2...

=SUMPRODUCT(1-ISNUMBER(SEARCH(A2:A6,E2)),--(B2:B6="Master"))

 

[Giordano Bruno]

Bloody brilliant Aladin!

Next time I'm in Rome I'll try to have you beatified.