Sumproduct by time & day of week

Is it just a type "Saturday" in the I column or is it a formatted date?

If the former, then probably:


=SUMPRODUCT(--(D2:D1000=A2),(H2:$H1000>="17:00:00")+(H2:$H1000<="00:00:00"),--(I2:I1000="Saturday"),S2:S1000)

or if the latter, then probably this can be one option:

Please Login or Register  to view this content.

In addition to above re: missing coercion...

Your Col I range is not the same dimension as the others - this would lead to #VALUE! error

If that does not resolve your error the next question would be:

Does your Col I range contain dates or Text strings ? ie =ISNUMBER(I2) returns what ?

Edited considering comments above.....

Why do you have this part?

$H2:$H1000<="00:00:00"

how can the time be less than zero? If you just want times between 17:00 and midnight then you don't need that part.

You also need to convert "17:00" by using some sort of co-ercer like +0 otherwise everything will be TRUE and you'll get the wrong result....

So incorporating those and the change to column I range as suggested above......

=SUMPRODUCT((D2:D1000=A2)*(H2:$H1000>="17:00"+0)*(I2:I1000="Saturday"),S2:S1000)

There are 3 things you definitely need to fix

1) incorrect range - range for column I should start at row 2 as the other ranges do, i.e.

(I2:I1000="Saturday")

2) A "co-ercer" like -- before that part, i.e. (I2:I1000="Saturday")

Those 2 both suggested by NBVC and DO

3) you also need co-ercer on the time values so adapting the vaersion posted by NBVC that would become

=SUMPRODUCT(--(D2:D1000=A2),(H2:$H1000>="17:00:00"+0)+(H2:$H1000<="00:00:00"+0),--(I2:I1000="Saturday"),S2:S1000)

In my post above I used a slightly different syntax and took out the second time check which is redundant here (but you might need that if you want to check for 17:00 to 05:00)

If you click on any cell within column I that has a "Saturday" in it.. what do you see in the formula Bar?