Conditional Sum based upon two conditions

[Ian Manning]

Hi,

I would be very grateful for any help with this issue! I'm trying to
sum the numbers in one column which meet two criteria in 2 *other*
columns. An example:

Column 1 Column 2 Column 3
Patient 08:45 8
Prescriber 09:55 4
Patient 10:40 5
Patient 08:33 4
Pharmacy 09:44 5

Column 1 has a value of either "Patient" "Prescriber" or "Pharmacy".
I want to lookup "Patient". Column two has time values, and I want to
sum for each hour, eg first off all from 08:00-08:59. In other words
I want to sum the values in column 3 for Patients between 08:00-08:59
then in a seperate cell for Patients between 09:00-09:59 etc etc.

I've tried using sumifs and/or sums and ifs, eg:

=if(C:C<09:00,sumif(B:B,"Patient",D),0) but excel doesn't like that

=sumif(B:B,"Patient",AND(C:C<09:00),D) <-- presumably a missuse of
the AND function?

Any help VERY gratefully appreciated.

 

[Jerry W. Lewis]

=SUMPRODUCT((B1:B65535="Patient")*(C1:C65535>=TIME(8,0,0))*(C1:C65535<TIME(9,0,0)),D165535)

This type of formula cannot refer to an entire column (e.g. B:B) but it
can refer to almost an entire column (e.g. B1:B65535 or B2:B65536)

Jerry