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))

ID	Day	Code	Value	TRUE FALSE
131	Thursday	2	22.4	TRUE
131	Friday	2	22.0	TRUE
131	Saturday	2	24.8	TRUE
131	Wednesday	2	25.6	FALSE
131	Monday	2	22.9	FALSE
131	Tuesday	2	23.1	FALSE
131	Sunday	2	0.0	FALSE
132	Thursday	2	23.5	TRUE
132	Friday	2	28.3	TRUE
132	Saturday	2	30.9	TRUE
132	Tuesday	2	29.4	FALSE
132	Wednesday	2	30.2	FALSE
132	Monday	2	27.9	FALSE
132	Sunday	2	0.0	FALSE
133	Thursday	2	36.8	TRUE
133	Friday	2	34.3	TRUE
133	Saturday	2	33.1	TRUE
133	Tuesday	2	37.9	FALSE
133	Monday	2	41.2	FALSE
133	Wednesday	2	31.5	FALSE
133	Sunday	2	0.0	FALSE
134	Thursday	2	34.9	TRUE
134	Friday	2	25.7	TRUE
134	Saturday	2	20.7	TRUE

<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> </tbody>

 

[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)),)