# Solved: Excel - COUNTIFS to include OR



## Dumbblond (Jul 23, 2009)

I am trying to create a formula in Column C that will give me a total result using 2 pieces of information in Column A and one piece of information in Column B.
As the spreadsheet I am using contains confidential clinic information I will give you an example of what I am trying to achieve. 
I have a spreadsheet containing the list of names of Doctors in Column A. 
In Column B I have a list of different Clinics that they run.
I need to know how many times Dr Black and Dr Brown were at the Bellevue Clinic. 
I can use the formula *COUNTIFS(A2:A29, "Dr Brown", B2:B29, "Bellevue")* to get the required result for one doctor, but can't get it for two. I have tried typing in the formula *COUNTIFS(A2:A29, "Dr Brown", A2:A29, "Dr Black", B2:B29, "Bellevue")* but the result comes back as zero. Can you help please? I presume I need an OR criteria in the formula, but it won't let me do this.


----------



## DJh6064 (Apr 20, 2009)

try just adding both the countifs statements like this:
=COUNTIFS(A2:A29,"Dr Bworn")+COUNTIFS(B2:B29,"Bellevue")


----------



## slurpee55 (Oct 20, 2004)

Or you could do a pivot table on the combined data - first, make a column with the formula
=(A2&" "&B2) and drag it down. Then you will get responses like "Dr Brown Bellevue" and "Dr Brown Otherhospital".
A pivot table would then give you a count of each combination.


----------



## Dumbblond (Jul 23, 2009)

Brilliant - thank you very much - it worked!


----------



## slurpee55 (Oct 20, 2004)

Great! Please use the button at the top of the page to mark this thread as Solved. 
And welcome to the Forum!


----------

