Sumifs Multiple

[billandrew]

Good evening all

Trying to sum/combine Column D values.

1. Monday & Tuesday
2.Thursday & Wednesday
3. Friday & Saturday

If Column E = TRUE and Column C = 2 for each unique ID.

attempted formula = =+IF(AND($E2=TRUE,$C2=2),IF(OR($B2="Monday",$B2="Tuesday"),SUMIFS(D$2:D$57,$B$2:$B$57,"Monday",$A$2:$A$57,$A2)+SUMIFS(D$2:D$57,$B$2:$B$57,"Tuesday",$A$2:$A$57,$A2),0))

[TABLE="width: 339"]
<colgroup><col width="70" style="width: 53pt;"> <col width="100" style="width: 75pt;"> <col width="70" style="width: 53pt;" span="2"> <col width="142" style="width: 107pt;"> <tbody>[TR]
[TD="width: 70, bgcolor: transparent"]ID[/TD]
[TD="width: 100, bgcolor: transparent"]Day[/TD]
[TD="width: 70, bgcolor: transparent"]Code[/TD]
[TD="width: 70, bgcolor: transparent"]Value[/TD]
[TD="width: 142, bgcolor: transparent"]TRUE FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]131[/TD]
[TD="bgcolor: transparent"]Thursday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]22.4[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]131[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]22.0[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]131[/TD]
[TD="bgcolor: transparent"]Saturday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]24.8[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]131[/TD]
[TD="bgcolor: transparent"]Wednesday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]25.6[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]131[/TD]
[TD="bgcolor: transparent"]Monday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]22.9[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]131[/TD]
[TD="bgcolor: transparent"]Tuesday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]23.1[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]131[/TD]
[TD="bgcolor: transparent"]Sunday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]0.0[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]132[/TD]
[TD="bgcolor: transparent"]Thursday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]23.5[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]132[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]28.3[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]132[/TD]
[TD="bgcolor: transparent"]Saturday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]30.9[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]132[/TD]
[TD="bgcolor: transparent"]Tuesday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]29.4[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]132[/TD]
[TD="bgcolor: transparent"]Wednesday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]30.2[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]132[/TD]
[TD="bgcolor: transparent"]Monday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]27.9[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]132[/TD]
[TD="bgcolor: transparent"]Sunday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]0.0[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]133[/TD]
[TD="bgcolor: transparent"]Thursday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]36.8[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]133[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]34.3[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]133[/TD]
[TD="bgcolor: transparent"]Saturday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]33.1[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]133[/TD]
[TD="bgcolor: transparent"]Tuesday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]37.9[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]133[/TD]
[TD="bgcolor: transparent"]Monday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]41.2[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]133[/TD]
[TD="bgcolor: transparent"]Wednesday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]31.5[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]133[/TD]
[TD="bgcolor: transparent"]Sunday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]0.0[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]134[/TD]
[TD="bgcolor: transparent"]Thursday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]34.9[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]134[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]25.7[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]134[/TD]
[TD="bgcolor: transparent"]Saturday[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]20.7[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

 

[billandrew]

for each id 132 (column A). If column E is true & column c is equal to 2 sum column D and place the result adjacent to the TRUE value

 

[Aladin Akyurek]

for each id 132 (column A). If column E is true & column c is equal to 2 sum column D and place the result adjacent to the TRUE value

I don't think this is clear...

Is this what you want?

=SUMIFS(D:D,A:A,X2,C:C,2)

where X2 houses an id of interest.

 

[snjpverma]

how do you get those "Monday, Tuesday,.......
are those actual dates formatted in a way that they appear as "Days" or are they just a text

 

[snjpverma]

Try this confirmed with Ctrl + Shift + Enter

=IF(E2=FALSE,"No Value needed",SUM(SUMIFS(D$2:D$25,B$2:B$25,IF(OR(B2="Monday",B2="Tuesday"),{"Monday","Tuesday"},IF(OR(B2="Wednesday",B2="Thursday"),{"Wednesday","Thursday"},{"Friday","Saturday"})),C$2:C$25,2)))

 

[snjpverma]

Or this with
Just Enter

=IF(E2=FALSE,"No Value needed",SUMPRODUCT(SUMIFS(D$2:D$25,B$2:B$25,IF(OR(B2="Monday",B2="Tuesday"),{"Monday","Tuesday"},IF(OR(B2="Wednesday",B2="Thursday"),{"Wednesday","Thursday"},{"Friday","Saturday"})),C$2:C$25,2)))

 

[snjpverma]

if you want to include the criteria of ID number as well, you can do it like this:

=IF(E2=FALSE,"No Value needed",SUMPRODUCT(SUMIFS(D$2:D$7,$A$2:$A$7,A2,B$2:B$7,IF(OR(B2="Monday",B2="Tuesday"),{"Monday","Tuesday"},IF(OR(B2="Wednesday",B2="Thursday"),{"Wednesday","Thursday"},{"Friday","Saturday"})),C$2:C$7,2)))

 

[billandrew]

snjpverma

This is exactly what I need. Post 16!


Thank You all so much.

 

[billandrew]

I was able to come up with the below, however much longer.

=+IF(AND($E2=TRUE,$C2=2),IF(OR($B2="Monday",$B2="Tuesday"),SUMIFS(D$2:D$57,$B$2:$B$57,"Monday",$A$2:$A$57,$A2)+SUMIFS(D$2:D$57,$B$2:$B$57,"Tuesday",$A$2:$A$57,$A2),IF(AND($E2=TRUE,$C2=2),IF(OR($B2="Wednesday",$B2="Thursday"),SUMIFS(D$2:D$57,$B$2:$B$57,"Wednesday",$A$2:$A$57,$A2)+SUMIFS(D$2:D$57,$B$2:$B$57,"Thursday",$A$2:$A$57,$A2),IF(AND($E2=TRUE,$C2=2),IF(OR($B2="Saturday",$B2="Friday"),SUMIFS(D$2:D$57,$B$2:$B$57,"Saturday",$A$2:$A$57,$A2)+SUMIFS(D$2:D$57,$B$2:$B$57,"Friday",$A$2:$A$57,$A2),0))))))+0

 

[snjpverma]

You can try the below shorter versions

=IF(E2,SUMPRODUCT(SUMIFS(D$2:D$7,$A$2:$A$7,A2,B$2:B$7,VLOOKUP(left(B2,2)&"*",{"Mo","Tu";"Tu","Mo";"We","Th";"Th","We";"Fr","Sa";"Sa","Fr"}&"*",{1,2},0),C$2:C$7,2)),)

OR

=IF(E2,SUMPRODUCT(SUMIFS(D$2:D$7,$A$2:$A$7,A2,B$2:B$7,IF(OR(B2={"Monday","Tuesday"}),{"Mo*","Tu*"},IF(OR(B2={"Wednesday","Thursday"}),{"We*","Th*"},{"F*","Sa*"})),C$2:C$7,2)),)

 

[snjpverma]

OR even shorter

=IF(E2,SUMPRODUCT(SUMIFS(D$2:D$7,$A$2:$A$7,A2,B$2:B$7,VLOOKUP(LEFT(B2,3)&"*",TEXT({2,3;3,2;4,5;5,4;6,7;7,6},"ddd")&"*",{1,2},0),C$2:C$7,2)),)