Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •