sum values selected variance in the list

[jeditetsu]

Dear mrexcel community,
My questions is;
[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"] [/TD]
[TD="width: 64"]Jan[/TD]
[TD="width: 64"]Feb[/TD]
[TD="width: 64"]Mar[/TD]
[TD="width: 64"]Apr[/TD]
[/TR]
[TR]
[TD]Item 1 [/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]


Suppose there is a dropdown menu for item 1 and 2.
When selected 1 I want to sum all the months.
What is the shortest way of doing it?

thanks a lot!
tim

 

[Marcílio_Lobão]

jeditetsu, Good morning.

Supposing your scenario, try to use this formula at H2

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: center"]Apr[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]ITEM 1[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]ITEM 1[/TD]
[TD="align: center"]=SUMPRODUCT((A2:A3=G2)*(B2:E3))[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]ITEM 2[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Please, tell us if it worked as you desire.
I hope it helps.

 

[jeditetsu]

hello Marcilio,

Exactly, just the formula I needed!
thank you very much and hope you`ll have a beautiful day.

best regards
timur