SUMPRODUCTS (Multiple COUNTIF's) and time values

Posted by Tom Boyd on January 09, 2002 11:54 AM

How do I make a time value act as a criteria in a SUMPRODUCT? Assuming the time format is 1:00 PM, the entry in the cell is 1:00:00 PM, and the text format is 0.541666666666667.

This is the formula I'm starting with, but I cannot make criteria2 recognize times.

=SUMPRODUCT((L6:L187="criteria1")*(G6:G187="criteria2"))

Thanks in advance!

-Tom

Posted by Aladin Akyurek on January 09, 2002 12:12 PM

Tom --

What is in L6:L187 and what is in G6:G187?

G6:G187 appears to house times like 1:00:00. Right?

Criteria2 appears to be a time like 1:00. What is criteria1?

My guess is that, when you put criteria1 in say A1 and criteria in A2,

=SUMPRODUCT((L6:L187=A1)*(G6:G187=A2))

should simply work.

Aladin

======

Posted by Mark W. on January 09, 2002 12:15 PM

=SUMPRODUCT((L6:L187="criteria1")*(G6:G187="1:00 P"+0))

Posted by Scott on January 09, 2002 12:16 PM

The Sumproduct should recognize the cell value (1:00Pm would be .0416666666666667) Check it against this number.

Posted by Mark W. on January 09, 2002 12:20 PM

0.0416666666666667 in 1:00 AM! (nt)

Posted by Tom Boyd on January 09, 2002 12:25 PM

That did the trick. Thanks!

-Tom

Posted by Scott on January 09, 2002 12:38 PM

Re: 0.0416666666666667 in 1:00 AM! (nt)

Isn't this what I put? Or am I going blind with all the 6's. :)

Posted by Mark W. on January 09, 2002 12:42 PM

Tom wanted 1:00 PM or 0.5+0.0416666666666667 (nt)

Posted by Scott on January 09, 2002 12:47 PM

I see now!! -blame it on old age- Thanks Mark (nt)

Posted by Mark W. on January 09, 2002 12:57 PM

This also demonstrates the advantages...

...of using a date literal (e.g. "1:00 P") as a
constant in a formula (and then coercing it into
a numeric values by adding zero... "1:00 P"+0).
This approach is much more readable than...
0.0416666666666667 or .5416666666666667