Sumif or Sumproduct with 3 conditions or criteria

[Jbooze]

I have searched this forum and not found a formulat that will work. I need to check 3 conditions and if all three are true sum the values in a range of cells. One formula I have tried is:

=SUMPRODUCT((Configuration!A3:A370=B11)*(Configuration!C3:C370>=(T3)*(Configuration!C3:C370<=(U3)*(Configuration!D3:D370))))

Where B11 is the day of week (Monday, Tuesday, etc.), T3 is the starting date in the range and U3 is the end date in the range. If all 3 are true in columns A and the two conditions for C, then sum cells for True rows in Configuration!D3:D370

Any ideas?

 

[Jbooze]

isnumber(B11) = False. I tried placing numerical values 1 through 7 in one at a time and the value remains zero. I see where when typing in WEEKDAY, the pop up shows that it will return a numerical value. Since changing the word "Monday" to a numerical value did not populate a value greater than zero what could it be? isnumber Configuration column C the date = True.

 

[Jbooze]

My dates are formated as m/d//yyyy instead of dd/mm/yyyy. Could that be the cause?

 

[scottylad2]

no that wouldn't be the case, if you care to send me a sample and I'll check for you. I'll message you my email address

 

[Aladin Akyurek]

I have searched this forum and not found a formulat that will work. I need to check 3 conditions and if all three are true sum the values in a range of cells. One formula I have tried is:

=SUMPRODUCT((Configuration!A3:A370=B11)*(Configuration!C3:C370>=(T3)*(Configuration!C3:C370<=(U3)*(Configuration!D3:D370))))

Where B11 is the day of week (Monday, Tuesday, etc.), T3 is the starting date in the range and U3 is the end date in the range. If all 3 are true in columns A and the two conditions for C, then sum cells for True rows in Configuration!D3:D370

Any ideas?

If you correct the formula for parens, you would get:

=SUMPRODUCT((Configuration!A3:A370=B11)*(Configuration!C3:C370>=T3)*(Configuration!C3:C370<=U3)*Configuration!D3:D370)

or, written a bit differently...

=SUMPRODUCT(--(Configuration!A3:A370=B11),--(Configuration!C3:C370>=T3),--(Configuration!C3:C370<=U3),Configuration!D3:D370)

Does this work?

 

[scottylad2]

=SUMPRODUCT(--(Configuration!C3:C370<>""),--(Weekday(Configuration!C3:C370,2)=B11),--(Configuration!C3:C370>=T3),--(Configuration!C3:C370<=U3),Configuration!D3:D370)

Try this, it should now work. B11 still needs to be numeric, ie 1 through 7

 

[Jbooze]

Aladin, the second formula of yours worked and Scottylad, your new version works perfectly too with the numerical value on the days of the week. I am in business now! Thanks so much to both of you for taking the time to resolve this.

 

[Aladin Akyurek]

Aladin, the second formula of yours worked and Scottylad, your new version works perfectly too with the numerical value on the days of the week. I am in business now! Thanks so much to both of you for taking the time to resolve this.

You are welcome. Thanks for providing feedback.