Cumulative total across columns and specifying which columns to include using a variable

[orangebloss]

Hi

I've got a set of data that each week I need to run a report to see what the cumulative total should be
Is there a way I can do this in a formula?

A	B	C	D	E
1	17/04
2	17/02	17/03	17/04	17/05
3	15	10	20	10

I would like to perform a total based on the value in B1 :
Sum values that are in the columns preceeding the column with the title in B1 (i.e. 15+10 )
Sum values that are in the columns from the column with the title in B1 (i.e. 20+10)

Any advice?

Thanks in advance

C

 

[Jeffrey Mahoney]

Somebody will probably come up with an easier solution, but until that happens, please try this. I assumed you wanted to eventually add up more rows of data in each column so I created some dummy data. Cells A2 and B2 are the answer. Column A and B are added to summarize the values. Range C2:F2 are formulas to test based on the value in C1.

Jeff


Excel 2012

	A	B	C	D	E	F
1			17/04
2	697	782	Before	Before	After	After
3	Before	After	17/02	17/03	17/04	17/05
4	25	30	15	10	20	10
5	27	32	16	11	21	11
6	29	34	17	12	22	12
7	31	36	18	13	23	13
8	33	38	19	14	24	14
9	35	40	20	15	25	15
10	37	42	21	16	26	16
11	39	44	22	17	27	17
12	41	46	23	18	28	18
13	43	48	24	19	29	19
14	45	50	25	20	30	20
15	47	52	26	21	31	21
16	49	54	27	22	32	22
17	51	56	28	23	33	23
18	53	58	29	24	34	24
19	55	60	30	25	35	25
20	57	62	31	26	36	26

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula A2 =SUM(A4:A20) B2 =SUM(B4:B20) C2 =IF(C3<$C$1,"Before","After") D2 =IF(D3<$C$1,"Before","After") E2 =IF(E3<$C$1,"Before","After") F2 =IF(F3<$C$1,"Before","After") A4 =SUMIFS(C4:F4,$C$2:$F$2,A$3) B4 =SUMIFS(C4:F4,$C$2:$F$2,$B$3) A5 =SUMIFS(C5:F5,$C$2:$F$2,A$3) B5 =SUMIFS(C5:F5,$C$2:$F$2,$B$3) A6 =SUMIFS(C6:F6,$C$2:$F$2,A$3) B6 =SUMIFS(C6:F6,$C$2:$F$2,$B$3) A7 =SUMIFS(C7:F7,$C$2:$F$2,A$3) B7 =SUMIFS(C7:F7,$C$2:$F$2,$B$3) A8 =SUMIFS(C8:F8,$C$2:$F$2,A$3) B8 =SUMIFS(C8:F8,$C$2:$F$2,$B$3) A9 =SUMIFS(C9:F9,$C$2:$F$2,A$3) B9 =SUMIFS(C9:F9,$C$2:$F$2,$B$3) A10 =SUMIFS(C10:F10,$C$2:$F$2,A$3) B10 =SUMIFS(C10:F10,$C$2:$F$2,$B$3) A11 =SUMIFS(C11:F11,$C$2:$F$2,A$3) B11 =SUMIFS(C11:F11,$C$2:$F$2,$B$3) A12 =SUMIFS(C12:F12,$C$2:$F$2,A$3) B12 =SUMIFS(C12:F12,$C$2:$F$2,$B$3) A13 =SUMIFS(C13:F13,$C$2:$F$2,A$3) B13 =SUMIFS(C13:F13,$C$2:$F$2,$B$3) A14 =SUMIFS(C14:F14,$C$2:$F$2,A$3) B14 =SUMIFS(C14:F14,$C$2:$F$2,$B$3) A15 =SUMIFS(C15:F15,$C$2:$F$2,A$3) B15 =SUMIFS(C15:F15,$C$2:$F$2,$B$3) A16 =SUMIFS(C16:F16,$C$2:$F$2,A$3) B16 =SUMIFS(C16:F16,$C$2:$F$2,$B$3) A17 =SUMIFS(C17:F17,$C$2:$F$2,A$3) B17 =SUMIFS(C17:F17,$C$2:$F$2,$B$3) A18 =SUMIFS(C18:F18,$C$2:$F$2,A$3) B18 =SUMIFS(C18:F18,$C$2:$F$2,$B$3) A19 =SUMIFS(C19:F19,$C$2:$F$2,A$3) B19 =SUMIFS(C19:F19,$C$2:$F$2,$B$3) A20 =SUMIFS(C20:F20,$C$2:$F$2,A$3) B20 =SUMIFS(C20:F20,$C$2:$F$2,$B$3) C5 =1+C4 D5 =1+D4 E5 =1+E4 F5 =1+F4 C6 =1+C5 D6 =1+D5 E6 =1+E5 F6 =1+F5 C7 =1+C6 D7 =1+D6 E7 =1+E6 F7 =1+F6 C8 =1+C7 D8 =1+D7 E8 =1+E7 F8 =1+F7 C9 =1+C8 D9 =1+D8 E9 =1+E8 F9 =1+F8 C10 =1+C9 D10 =1+D9 E10 =1+E9 F10 =1+F9 C11 =1+C10 D11 =1+D10 E11 =1+E10 F11 =1+F10 C12 =1+C11 D12 =1+D11 E12 =1+E11 F12 =1+F11 C13 =1+C12 D13 =1+D12 E13 =1+E12 F13 =1+F12 C14 =1+C13 D14 =1+D13 E14 =1+E13 F14 =1+F13 C15 =1+C14 D15 =1+D14 E15 =1+E14 F15 =1+F14 C16 =1+C15 D16 =1+D15 E16 =1+E15 F16 =1+F15 C17 =1+C16 D17 =1+D16 E17 =1+E16 F17 =1+F16 C18 =1+C17 D18 =1+D17 E18 =1+E17 F18 =1+F17 C19 =1+C18 D19 =1+D18 E19 =1+E18 F19 =1+F18 C20 =1+C19 D20 =1+D19 E20 =1+E19 F20 =1+F19 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>