COUNTIF with multiple criteria AND variables in search string...

[Jonplps]

Well, here's the issue: I need to count all the entries that appear from 1) a chemotherapeutics department and 2) between the hours of 8:00 AM and 8:59 AM from a data dump file. So far,

=SUMPRODUCT(('Raw Data'!H$853:H$34698="10:30 AM")*('Raw Data'!E$853:H$34698="FLW CHEMO"))

Works perfectly, but only counts the minute specified. Normally, most formulas would allow astericks within the search string so "10:** AM" would return all values within that hour, however this particular formula is not cooperating. . .

Please don't tell me I'll have to do 60 calculations for each hour . . . Is there a more efficient method to do this?

Basically I have to graph the orders from this department for each hour of a shift for the last month. I have a whole mess of raw data.
Any help would be greatly appreciated.

 

[T. Valko]

Well, here's the issue: I need to count all the entries that appear from 1) a chemotherapeutics department and 2) between the hours of 8:00 AM and 8:59 AM from a data dump file. So far,

=SUMPRODUCT(('Raw Data'!H$853:H$34698="10:30 AM")*('Raw Data'!E$853:H$34698="FLW CHEMO"))

Works perfectly, but only counts the minute specified. Normally, most formulas would allow astericks within the search string so "10:** AM" would return all values within that hour, however this particular formula is not cooperating. . .

Please don't tell me I'll have to do 60 calculations for each hour . . . Is there a more efficient method to do this?

Basically I have to graph the orders from this department for each hour of a shift for the last month. I have a whole mess of raw data.
Any help would be greatly appreciated.

One way...

Use cells to hold the criteria:

• A1 = lower time boundary = 8:00 AM
• B1 = upper time boundary = 8:59 AM
• C1 = FLW CHEMO

Then:

=SUMPRODUCT(--('Raw Data'!H$853:H$34698>=A1),--('Raw Data'!H$853:H$34698<=B1),--('Raw Data'!E$853:H$34698=C1))

 

[Jonmo1]

Welcome to the board...

Try

=SUMPRODUCT((HOUR('Raw Data'!H$853:H$34698)=10)*('Raw Data'!E$853:H$34698="FLW CHEMO"))

 

[Jonplps]

Perfect! I love you guys! lol

 

[T. Valko]

One way...


Use cells to hold the criteria:

• A1 = lower time boundary = 8:00 AM
• B1 = upper time boundary = 8:59 AM
• C1 = FLW CHEMO

Then:

=SUMPRODUCT(--('Raw Data'!H$853:H$34698>=A1),--('Raw Data'!H$853:H$34698<=B1),--('Raw Data'!E$853:H$34698=C1))

If you're using Excel 2007 or later...

=COUNTIFS('Raw Data'!H$853:H$34698,">="&A1,'Raw Data'!H$853:H$34698,"<="&B1,'Raw Data'!E$853:H$34698,C1)

The COUNTIFS version is more efficient.