# Thread: Sumproduct with multiple criteria (2003)

1. ## Sumproduct with multiple criteria (2003)

I learned about sumproduct yesterday, but now have multiple criteria to meet. Is there a way to do this? I've been trying

=SUMPRODUCT((AND((WEEKDAY(B2:B337)=1),(HOUR(B2:B33 7)>HOUR(start)))*C2:C337)) but get a value error, although either condition on their own works. I've also tried an array with sum and a nested if, but can't get that to work.

=SUM(IF(AND((HOUR(B2:B337)<HOUR(stop)), (HOUR(B2:B337)>=HOUR(start)), (NOT(OR((WEEKDAY(B2:B337)=1), (WEEKDAY(B2:B337)=7))))), C2:C337, "0"))

Can anyone tell me what trick I'm missing? I have used the if formula above to set up a new column and then have summed the column so can get to my answer. I just wanted to learn more about trying to write the conditional formula.

2. ## Re: Sumproduct with multiple criteria (2003)

Try this one (as a "normal" formula):

=SUMPRODUCT((WEEKDAY(B2:B337)=1)*(HOUR(B2:B337)>HO UR(start))*C2:C337)

And the second one, also as a "normal" formula:

=SUMPRODUCT((HOUR(B2:B337)<HOUR(stop))*(HOUR(B2:B3 37)>=HOUR(start))*(WEEKDAY(B2:B337)>1)*(WEEKDAY(B2 :B337)<7)*C2:C337)

3. ## Re: Sumproduct with multiple criteria (2003)

Thanks for the prompt reply - So I gather the multiplication operator takes the place of "And" for "matrix" type functions? Is that correct?

4. ## Re: Sumproduct with multiple criteria (2003)

You can use multiplication in this type of formula since TRUE = 1 and FALSE = 0. The calculation 1*1=1 corresponds excactly to TRUE AND TRUE = TRUE etc.

5. ## Re: Sumproduct with multiple criteria (2003)

Thanks I really appreciate it. Such a powerful tool with so much to learn ...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•