sum with dynamic columns

[rdev]

I have to do the following
If i select Mar , then all the 3 columns should be added , if I choose Fen then the two columns. Meaning the cummulative is dynamic based on month selected
Customer Jan Feb Mar
ACME 12300 9700 19400
Bluestar 5000 2700 9100
Gentech 18500 23700 31250
Inversys 0 16450 28650
Medsonic 11400 17500 34000

 

[ExcelElliott]

	A	B	C	D	E	F	G	H
Customer	Jan	Feb	Mar
ACME	Customer
Bluestar
Gentech	Selection
Inversys
Medsonic	Sum

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]12300[/TD]
[TD="align: right"]9700[/TD]
[TD="align: right"]19400[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5000[/TD]
[TD="align: right"]2700[/TD]
[TD="align: right"]9100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]18500[/TD]
[TD="align: right"]23700[/TD]
[TD="align: right"]31250[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]16450[/TD]
[TD="align: right"]28650[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]11400[/TD]
[TD="align: right"]17500[/TD]
[TD="align: right"]34000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H6[/TH]
[TD="align: left"]{=IFNA(SUMPRODUCT(INDEX(B2:D6,MATCH(H2,A2:A6,0),1):INDEX(B2:D6,MATCH(H2,A2:A6,0),MATCH(H4,B1:D1,0))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


Hope this helps, let me know if there are any questions or concerns.

 

[ExcelElliott]

Sorry forgot to mention that cell h2 & cell h4 are dropdowns, here's a better illustration:

	A	B	C	D	E	F	G	H
Customer	Jan	Feb	Mar
ACME	Customer	Inversys
Bluestar
Gentech	Selection	Mar
Inversys
Medsonic	Sum

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]12300[/TD]
[TD="align: right"]9700[/TD]
[TD="align: right"]19400[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5000[/TD]
[TD="align: right"]2700[/TD]
[TD="align: right"]9100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]18500[/TD]
[TD="align: right"]23700[/TD]
[TD="align: right"]31250[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]16450[/TD]
[TD="align: right"]28650[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]11400[/TD]
[TD="align: right"]17500[/TD]
[TD="align: right"]34000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]45100[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H6[/TH]
[TD="align: left"]{=IFNA(SUMPRODUCT(INDEX(B2:D6,MATCH(H2,A2:A6,0),1):INDEX(B2:D6,MATCH(H2,A2:A6,0),MATCH(H4,B1:D1,0))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]