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!

	January	February	March
apple	5	6	1
orange	8	2	2
orange	10	15	7
apple	4	8	5

<tbody>
</tbody>

	February (this is a drop down )
apple	14
orange	17

<tbody>
</tbody>

 

[FDibbins]

Try this...

	A​	B​	C​	D​
1​		January	February	March
2​	apple	5​	6​	1​
3​	orange	8​	2​	2​
4​	orange	10​	15​	7​
5​	apple	4​	8​	5​
6​
7​
8​		February
9​	apple	14​
10​	orange	17​

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
1		January	February	March
2	apple	5	6	1
3	orange	8	2	2
4	orange	10	15	7
5	apple	4	8	5
6
7
8		February
9	apple	14
10	orange	17

#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>
</tbody>

#DAE7F5 ;color: #161120">Sheet1​

Worksheet Formulas Cell Formula #DAE7F5 ;color: #161120">B9 =SUMPRODUCT((B$1:D$1=B$8)*(A$2:A$5=A9)*(B$2:D$5)) #FFFFFF ;border-collapse: collapse; border-color: #BBB"><thead> #DAE7F5 ;color: #161120"> </thead><tbody> </tbody>

#FFFFFF " ><tbody>
</tbody>

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...

	A​	B​	C​	D​
1​		January	February	March
2​	apple	5​	6​	1​
3​	orange	8​	2​	2​
4​	orange	10​	15​	7​
5​	apple	4​	8​	5​
6​
7​
8​		February
9​	apple	14​
10​	orange	17​

<tbody>
</tbody>

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