0

I'm currently trying to come up with a way to have excel look at a monthly schedule for my doctor's service rotations and to provide a summary of the services they're scheduled for and how many of each.

below is an example of how the schedule is formatted

Rotation service    Day 1         Day 2             Day 3          Day 4        Day 5

Cardiology       John/Jared   Jacob/Omer/Jerry  Mary/Richard    Gary/Beth   Monica/Richard

Consults          Omer/Mary   Richard/Monica    Jerry/John/Gary         Monica/Richard  

ER nights                        Omer/Monica    Omer/Jared

I'm attempting to summarize each providers rotations for a given month. So I attempted to create a formula that looks at the whole month and returns how many times a provider is scheduled for each rotation.

the end results should resemble this,

               Omer
Cardiology       1 
Consults         1
ER nights        2

I've used several different formulas and the closest I got was using the below code,

=IF(ISERROR(VLOOKUP($J$13,$A$1:$A$98,1,FALSE)),"Not there",COUNTIF($A1:$F98,$K$12&"*"))

This code is the exact code I used in my excel sheet, and it would check to see if value A($J$13 represents this value which would be the service rotation) and if so it would proceed to count how many times value B appears($K$12&"*" represents this value which is the Provider name) problem is it's counting how many times the name appears at all not just the same row/week that the value was found.

as a note the providers names share a cell so I had to use a wildcard in search criteria.

From my previous example the undesired result would look like so,

      Omer
 Cardiology   4
 Consults     4
 ER Nights    4

So I'm hoping I can get an answer to see if I can have the formula look at the whole month and only count Value B If Value A exist in the Same row as Value B but go through and count the providers name in each row that does contain both Values A and B, and return a sum amount for all qualifying rows.

I'd be very grateful for any input on this matter.

This seems like a lot to me so If you have any questions I'll do my best to answer them properly.

Joshua Lester
  • 3
  • 2

1 Answers1

0

I hope I've set-up my Excel file correctly (see image below), I only used a sample of data as you provided, from rows 2 to 4:

enter image description here

Then I used the formula:

=COUNTIF(INDEX($B$2:$F$4,MATCH($A9,$A$2:$A$4,0),),"*"&B$8&"*")

And you can see, the results match waht you wanted to achieve (see results in the image below) :

enter image description here

In MATCH($A9,$A$2:$A$4,0) formula:

Modify range to look for Service type by expanding the Range $A$2:$A$4.

In INDEX($B$2:$F$4,MATCH($A9,$A$2:$A$4,0),) formula:

Modify range to Count all places a certain Doctor appears by expanding the Range $B$2:$F$4.

Shai Rado
  • 33,032
  • 6
  • 29
  • 51