countif or sumproduct

**mmarff** · 03-20-2007, 09:57 AM

i have a column of times (amount of time, not time of day) (M1) and a column of job codes (S=service, R=return call, D=delivery) (L1).
i would like to calculate how many instances of time (M1) exceed four hours and also have a job code (L1) of "S". can someone help me?

**BigBas** · 03-20-2007, 09:59 AM

Try this formula:

=sumproduct(--(M1:M50>4),--(L1:L50="S"))

**mmarff** · 03-20-2007, 10:16 AM

that didn't work... does it matter that the column (M1) is formatted for time?

**mmarff** · 03-20-2007, 10:25 AM

the worksheet i am drawing from is called "march" and the column (M) changed to (N) (sorry for the changes) so my manipulated formula is as follows:

=SUMPRODUCT(--(March!N:N>4),--(March!L:L="S"))

it's still not working.
thanks for any help you can give!

**BigBas** · 03-20-2007, 10:42 AM

It is my understanding that sumproduct cannot refer to an entire column. So, instead of N:N (for example), use N1:N65000.

Just make sure that all your ranges are equal.

Let me know if that works.

**mmarff** · 03-20-2007, 10:56 AM

i changed the formula to this:

=SUMPRODUCT(--(March!N1:N65000>"4"),--(March!L1:L65000="S"))

it didn't come back with an error, but it didn't arrive at the correct result either, just a lonely little "0".

**mmarff** · 03-20-2007, 11:43 AM

the "4" was throwing me off (because it relates to time). i changed the formula to:

=SUMPRODUCT(--(March!N2:N1000>0.16666),--(March!L2:L1000="S"))

(0.16666 being the percentage of hours (4) in a day) and it works like a charm. thanks so much for your help!