Countifs, two criteria from a seperate sheet

[Starmusk10]

Hi all,

I have an existing sheet that is organized by sequential seal numbers. Each seal number has a date broken, the person that broke it and the medication that was used. In a different sheet i want to count how many time a person used a specific medication. Im having trouble getting any formula to work. Any suggestions would be greatly appreciated.

Thanks

Starmusk10

[ChemistB]

I suggest uploading a dummy worksheet (no confidential data) so we can see what information you have in what column (how your data is set up).

[Starmusk10]

I've attached the sample worksheet.

From Column E, How many times did provider 6 use Fentanyl.

[ChemistB]

Does provider come from Column C or Column E or either? If it's either, then if it appears in both on the same line i.e. C14 and E14 = 6, does that count as 1 or 2 or "it'll never happen that way?"

[Starmusk10]

Column E only. This column represents the provider who broke the seal and used meds. Column G represents the drug box used Column I is the med that was used.

[ChemistB]

Use COUNTIFS
For example on Sheet2
In A2 is provider
In B2 is drug
In C2
=COUNTIFS(Sheet1!$C$2:$C$22, A2, Sheet1!$I$2:$I$22, B2)

What if you'd asked about Midazolam? Row 17 had Midazolam twice. Would that count as 2 times? or is that a combination of drugs and would only be counted if you wanted "diazepam, Midazolam, Midalzolam"?

[Starmusk10]

It would be counted twice. Row 17 would be 1 Diazepam and 2 Midazolam.

[Starmusk10]

Take a look in row E, Thats the row that Provider 6 used Fentanyl and morphine in two seperate occasions but I only want to count the fentanyl use. How do I specify what ned I want counted.

[ChemistB]

Okay, try this formula in C2 instead

=SUMPRODUCT(--($I$3:$L$22=B2)*(E3:E22=A2))

[Starmusk10]

That one didnt work. It didnt specify what sheet to take the information from. or the specific drug to look for.

[ChemistB]

My mistake, should have read

=SUMPRODUCT(--(Sheet1!$I$3:$L$22=B2)*(Sheet1!E3:E22=A2))
The drug name is in B2, the provider is in A2

[Starmusk10]

That works! Thank you!