SUMPRODUCT() problem

[Jan Kronsell]

Hi!

I try to get this formula or someting like it to work.

=SUMPRODUCT((M17:M1115="245-B")*(R17:R1115="Not OK")*(H17:H1115=F2))


The problem is in the H-Column. It contains Dates and times like for
instance 26-04-2007 11:36:34
F2 in the formula contains 26-04-2007, and this is where the problem arises.
No values in H-column contains this exact value, so the formula retunrs a
zero. I have tried using VALUE() combined with INT(), and other combinations
on the h-column, but with no result aso far.

How should my formula look if I want to calulate the number of tests, done
by Dept. 245-B (M-column) on a certain date (H-column) no matter what time
of day, with status Not OK (R-column)

Jan

 

[Roger Govier]

Hi

Try
=SUMPRODUCT((M17:M1115="245-B")*(R17:R1115="Not
OK")*(TEXT(H17:H1115,"dd-mm-yyyy")=F2))

 

[Jan Kronsell]

Thnak you. It almost did it: :-9

=SUMPRODUCT((M17:M1115="245-B")*(R17:R1115="Not
OK")*(TEXT(H17:H1115,"dd-mm-yyyy")=TEXT(F2,"dd-mm-yyyy")))


was the solution.

Jan

 

[Dave Peterson]

I bet you meant:

=SUMPRODUCT((M17:M1115="245-B")
*(R17:R1115="Not OK")
*(TEXT(H17:H1115,"dd-mm-yyyy")=text(F2,"dd-mm-yyyy")))

Another option to ignore times in both column H and F2:

=SUMPRODUCT((M17:M1115="245-B")
*(R17:R1115="Not OK")
*(int(H17:H1115)=int(f2)))