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]

 

[cunningAce]

Hi,

Try this formula

=SUMIFS(D2:D25,B2:B25,"Monday",C2:C25,2,E2:E25,"True")+SUMIFS(D2:D25,B2:B25,"Tuesday",C2:C25,2,E2:E25,"True")

 

[snjpverma]

Try this

=SUM(SUMIFS(D2:D25,B2:B25,{"Monday","Tuesday"},C2:C25,2,E2:E25,"True"))

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

ID​

[/TD]
[TD]

Day​

[/TD]
[TD]

Code​

[/TD]
[TD]

Value​

[/TD]
[TD]

TRUE FALSE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

131​

[/TD]
[TD]Thursday[/TD]
[TD]

2​

[/TD]
[TD]

22.4​

[/TD]
[TD]

TRUE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Monday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

131​

[/TD]
[TD]Friday[/TD]
[TD]

2​

[/TD]
[TD]

22​

[/TD]
[TD]

TRUE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Saturday[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

131​

[/TD]
[TD]Saturday[/TD]
[TD]

2​

[/TD]
[TD]

24.8​

[/TD]
[TD]

TRUE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

131​

[/TD]
[TD]

0​

[/TD]
[TD]

22.4​

[/TD]
[TD]

46.8​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

131​

[/TD]
[TD]Wednesday[/TD]
[TD]

2​

[/TD]
[TD]

25.6​

[/TD]
[TD]

FALSE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

132​

[/TD]
[TD]

23.5​

[/TD]
[TD]

0​

[/TD]
[TD]

59.2​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

131​

[/TD]
[TD]Monday[/TD]
[TD]

2​

[/TD]
[TD]

22.9​

[/TD]
[TD]

FALSE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

133​

[/TD]
[TD]

0​

[/TD]
[TD]

36.8​

[/TD]
[TD]

67.4​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

131​

[/TD]
[TD]Tuesday[/TD]
[TD]

2​

[/TD]
[TD]

23.1​

[/TD]
[TD]

FALSE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

134​

[/TD]
[TD]

0​

[/TD]
[TD]

34.9​

[/TD]
[TD]

46.4​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

131​

[/TD]
[TD]Sunday[/TD]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[TD]

FALSE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

132​

[/TD]
[TD]Tuesday[/TD]
[TD]

2​

[/TD]
[TD]

23.5​

[/TD]
[TD]

TRUE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

132​

[/TD]
[TD]Friday[/TD]
[TD]

2​

[/TD]
[TD]

28.3​

[/TD]
[TD]

TRUE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

132​

[/TD]
[TD]Saturday[/TD]
[TD]

2​

[/TD]
[TD]

30.9​

[/TD]
[TD]

TRUE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

132​

[/TD]
[TD]Tuesday[/TD]
[TD]

2​

[/TD]
[TD]

29.4​

[/TD]
[TD]

FALSE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

132​

[/TD]
[TD]Wednesday[/TD]
[TD]

2​

[/TD]
[TD]

30.2​

[/TD]
[TD]

FALSE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

132​

[/TD]
[TD]Monday[/TD]
[TD]

2​

[/TD]
[TD]

27.9​

[/TD]
[TD]

FALSE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

132​

[/TD]
[TD]Sunday[/TD]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[TD]

FALSE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

133​

[/TD]
[TD]Thursday[/TD]
[TD]

2​

[/TD]
[TD]

36.8​

[/TD]
[TD]

TRUE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

133​

[/TD]
[TD]Friday[/TD]
[TD]

2​

[/TD]
[TD]

34.3​

[/TD]
[TD]

TRUE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]

133​

[/TD]
[TD]Saturday[/TD]
[TD]

2​

[/TD]
[TD]

33.1​

[/TD]
[TD]

TRUE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]

133​

[/TD]
[TD]Tuesday[/TD]
[TD]

2​

[/TD]
[TD]

37.9​

[/TD]
[TD]

FALSE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]

133​

[/TD]
[TD]Monday[/TD]
[TD]

2​

[/TD]
[TD]

41.2​

[/TD]
[TD]

FALSE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]

133​

[/TD]
[TD]Wednesday[/TD]
[TD]

2​

[/TD]
[TD]

31.5​

[/TD]
[TD]

FALSE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]

133​

[/TD]
[TD]Sunday[/TD]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[TD]

FALSE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]

134​

[/TD]
[TD]Thursday[/TD]
[TD]

2​

[/TD]
[TD]

34.9​

[/TD]
[TD]

TRUE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]

134​

[/TD]
[TD]Friday[/TD]
[TD]

2​

[/TD]
[TD]

25.7​

[/TD]
[TD]

TRUE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]

134​

[/TD]
[TD]Saturday[/TD]
[TD]

2​

[/TD]
[TD]

20.7​

[/TD]
[TD]

TRUE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I4=SUMPRODUCT(SUMIFS($D$2:$D$25,$A$2:$A$25,$H4,$B$2:$B$25,I$2:I$3,$E$2:$E$25,$E$2,$C$2:$C$25,$C$2)) copy down and across

 

[billandrew]

A better explanation.


if Column E is TRUE, Column C is equal to 2. sum both values of the same ID TRUE AND FALSE.

In the table above I need to sum the value of ID 132 and place the result adjacent to the cell where there is a TRUE value in column E.

below is a smaller subset of the explanation. The result would be 52.86 the sum of values for ID 132.

The formula I am using above does work, however I would have to separate the Monday + Tuesday Wednesday + Thursday & Friday + Saturday data and then combine. Looking to perform with one function.


[TABLE="width: 473"]
<tbody>[TR]
[TD="align: right"]132[/TD]
[TD]Monday[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]23.5[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]132[/TD]
[TD]Friday[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]28.3[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]132[/TD]
[TD]Saturday[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30.9[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]132[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]29.4[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]132[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30.2[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"><col></colgroup>[/TABLE]

 

[Aladin Akyurek]

What is or are expected values given the sample of post #1 ?

 

[billandrew]

Original Post may not provide the most accurate explanation. The subsequent post (Post 6) provides a better example of what I am attempting to accomplish.

 

[snjpverma]

What value do you need adjacent to FALSE?

the below formula will result as "No Value needed" against all the False.

=IF(E2=False,"No Value needed",SUM(SUMIFS(D$2:D$25,B$2:B$25,{"Monday","Tuesday"},C$2:C$25,2)))

 

[billandrew]

a value of zero. Post 8 only provides sums for Monday & Tuesday

 

[Aladin Akyurek]

Original Post may not provide the most accurate explanation. The subsequent post (Post 6) provides a better example of what I am attempting to accomplish.

The same question for the data of post #6 ...