Dynamic Sumifs, help!

[crave4excel]

Hi everyone,
I'm in need of your expertise to create a formula that sums up the total based on a dynamic variable. Example below-

I have the months in columns that goes all the way to December, and apple and oranges repeat in rows. I'm trying to create a dynamic formula that calculates the SUM of each fruit, based on the variable, which is MONTH in this case. The variable will be a drop down menu that allows me to toggle the month. I'm not sure if SUMIFS or SUMPRODUCT is more appropriate, i've been pulling my hair out trying to get this work but nothing helps. I've tried INDEX & MATCH but it doesn't allow me to SUM up the values, instead of grabbing the first value it appears.

Please help!

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]January [/TD]
[TD]February [/TD]
[TD]March [/TD]
[/TR]
[TR]
[TD]apple
[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]February (this is a drop down )[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]17[/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

Try this...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]January[/td][td]February[/td][td]March[/td][/tr]

[tr][td]

2​

[/td][td]apple[/td][td]

5​

[/td][td]

6​

[/td][td]

1​

[/td][/tr]

[tr][td]

3​

[/td][td]orange[/td][td]

8​

[/td][td]

2​

[/td][td]

2​

[/td][/tr]

[tr][td]

4​

[/td][td]orange[/td][td]

10​

[/td][td]

15​

[/td][td]

7​

[/td][/tr]

[tr][td]

5​

[/td][td]apple[/td][td]

4​

[/td][td]

8​

[/td][td]

5​

[/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td]February[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]apple[/td][td]

14​

[/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]orange[/td][td]

17​

[/td][td][/td][td][/td][/tr]
[/table]
B9=SUMIF($A$2:$A$5,A9,INDEX($B$2:$D$5,,MATCH($B$8,$B$1:$D$1,0)))
copied down

 

[Jonmo1]

Try like this, assuming
the first table begins in A1 (January is in B1, apple in A2)
the 2nd table begins in B10 (month dropdown is in B10, apple is in A11)

=SUMIF($A$2:$A$5,$A11,INDEX($B$2:$M$5,0,MONTH(1&$B$10)))

 

[jtakw]

Hi,

This should work too:

Excel 2010

	A	B	C	D
January	February	March
apple
orange
orange
apple
February
apple
orange

#FFFFFF ;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col target="_blank" usertag.php?do='list&action=hash&hash=DAE7F5"' forum="" www.mrexcel.com="">#DAE7F5 " /><colgroup><col><col><col><col></colgroup><thead>
#DAE7F5 ;text-align: center;color: #161120">
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]

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

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

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

[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]7[/TD]

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

[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]

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

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

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

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

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

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

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

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

</tbody>

#DAE7F5 ;color: #161120">Sheet1​

[TABLE="width: 85%"]
#FFFFFF " ><tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
#FFFFFF ;border-collapse: collapse; border-color: #BBB"><thead>[TR]
#DAE7F5 ;color: #161120">[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10"]#DAE7F5 ;color: #161120">B9[/TH]
[TD="align: left"]=SUMPRODUCT((B$1:D$1=B$8)*(A$2:A$5=A9)*(B$2:D$5))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



B9 formula copied down.

PS: Hi Ford, I haven't been on for almost year and a half, hope everyone on the forum is doing well.

 

[crave4excel]

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

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

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

1​

[/TD]
[TD][/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[/TR]
[TR]
[TD]

2​

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

5​

[/TD]
[TD]

6​

[/TD]
[TD]

1​

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

3​

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

8​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

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

4​

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

10​

[/TD]
[TD]

15​

[/TD]
[TD]

7​

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

5​

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

4​

[/TD]
[TD]

8​

[/TD]
[TD]

5​

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

6​

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

7​

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

8​

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

9​

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

14​

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

10​

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

17​

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

B9=SUMIF($A$2:$A$5,A9,INDEX($B$2:$D$5,,MATCH($B$8,$B$1:$D$1,0)))
copied down

Thank you so much everyone!!! lifesaver!

 

[FDibbins]

Happy to help