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")
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
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")
Try this
=SUM(SUMIFS(D2:D25,B2:B25,{"Monday","Tuesday"},C2:C25,2,E2:E25,"True"))
A B C D E F G H I J K 1 ID Day Code Value TRUE FALSE 2 131Thursday 2 22.4 TRUEMonday Thursday Friday 3 131Friday 2 22 TRUETuesday Wednesday Saturday 4 131Saturday 2 24.8 TRUE 131 0 22.4 46.8 5 131Wednesday 2 25.6 FALSE 132 23.5 0 59.2 6 131Monday 2 22.9 FALSE 133 0 36.8 67.4 7 131Tuesday 2 23.1 FALSE 134 0 34.9 46.4 8 131Sunday 2 0 FALSE 9 132Tuesday 2 23.5 TRUE 10 132Friday 2 28.3 TRUE 11 132Saturday 2 30.9 TRUE 12 132Tuesday 2 29.4 FALSE 13 132Wednesday 2 30.2 FALSE 14 132Monday 2 27.9 FALSE 15 132Sunday 2 0 FALSE 16 133Thursday 2 36.8 TRUE 17 133Friday 2 34.3 TRUE 18 133Saturday 2 33.1 TRUE 19 133Tuesday 2 37.9 FALSE 20 133Monday 2 41.2 FALSE 21 133Wednesday 2 31.5 FALSE 22 133Sunday 2 0 FALSE 23 134Thursday 2 34.9 TRUE 24 134Friday 2 25.7 TRUE 25 134Saturday 2 20.7 TRUE
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
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.
132 Monday 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
What is or are expected values given the sample of post #1 ?
Assuming too much and qualifying too much are two faces of the same problem.
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.
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)))
a value of zero. Post 8 only provides sums for Monday & Tuesday
Assuming too much and qualifying too much are two faces of the same problem.
Like this thread? Share it with others