Sumifs on dynamic rows / columns

[dmessmer]

Hello, I'm trying to do either a sumifs or index(match() in order to sum a range of cells (multiple columns and rows) based upon a reference cell which can be modified to adjust the number of columns which are summed. For example, in the below table on the right are my results. Since A1 is 3 months, the fruit totals are summing columns B,C, & D so long as it's the appropriate type of fruit.

3	Months
Fruit	1/1/2017	2/1/2017	3/1/2017	4/1/2017		Fruit	Total
Apple	10	4	6	7		Apple	20
Banana	3	8	1	5		Banana	12
Orange	2	5	7	3		Orange	14

<tbody>
</tbody>

If my variable was 4 months the results would look like:

4	Months
Fruit	1/1/2017	2/1/2017	3/1/2017	4/1/2017		Fruit	Total
Apple	10	4	6	7		Apple	27
Banana	3	8	1	5		Banana	17
Orange	2	5	7	3		Orange	17

<tbody>
</tbody>

Any suggestions on how to sum up the totals of ranges based upon a reference cells which makes your range dynamic? Thanks for the help!

 

[MARZIOTULLIO]

<tbody>
</tbody>

	A​	B​	C​	D​	E​	F​	G​	H​
1​	3​	Months
2​
3​	Fruit	1/1/2017​	2/1/2017​	3/1/2017​	4/1/2017​		Fruit	Total
4​	Apple	10​	4​	6​	7​		Apple	20​
5​	Banana	3​	8​	1​	5​		Banana	12​
6​	Orange	2​	5​	7​	3​		Orange	14​

<tbody>
</tbody>

​

H4=SUMPRODUCT((MONTH($B$3:$E$3)<=$A$1)*($A$4:$A$6=$G4)*($B$4:$E$6)) copy down

for months 4 change 3 to 4 in a1

​

 

[brkkrkmz112]

Just an option. Needed to be pasted H4

=+SUMPRODUCT((G4=$A$4:$A$6)*INDEX($B$4;;):OFFSET($B$4;2;$A$1))

Hello, I'm trying to do either a sumifs or index(match() in order to sum a range of cells (multiple columns and rows) based upon a reference cell which can be modified to adjust the number of columns which are summed. For example, in the below table on the right are my results. Since A1 is 3 months, the fruit totals are summing columns B,C, & D so long as it's the appropriate type of fruit.

3	Months
Fruit	1/1/2017	2/1/2017	3/1/2017	4/1/2017		Fruit	Total
Apple	10	4	6	7		Apple	20
Banana	3	8	1	5		Banana	12
Orange	2	5	7	3		Orange	14

<tbody>
</tbody>

If my variable was 4 months the results would look like:

4	Months
Fruit	1/1/2017	2/1/2017	3/1/2017	4/1/2017		Fruit	Total
Apple	10	4	6	7		Apple	27
Banana	3	8	1	5		Banana	17
Orange	2	5	7	3		Orange	17

<tbody>
</tbody>

Any suggestions on how to sum up the totals of ranges based upon a reference cells which makes your range dynamic? Thanks for the help!

 

[dmessmer]

Thanks for the quick reply, Marziotullio. I thought that by posting a pretty simple example I'd be able to use the help & apply it to my bit more complex problem. Unfortunately I am unable to do so. Let me elaborate.

My scenario is:

		A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	3	Months																Results
2	Fruit	1/2017	2/2017	3/2017	4/2017	5/2017	6/2017	7/2017	8/2017	9/2017	10/2017	11/2017	12/2017	1/2018	2/2018	3/2018		1/2017	2/2017	3/2017	4/2017	5/2017	6/2017	Etc.
3	Apple	10	4	6	7	8	3	7	11	14	12	5	7	9	2	10		20	17	21	18	18	21
4	Banana	8	9	6	3	11	4	7	10	5	7	6	3	7	8	4		23	18	20	18	22	21
5	Orange	3	5	7	8	9	6	4	10	7	10	4	7	6	3	7		15	20	24	23	19	20

<tbody>
</tbody>

I think that the original solution you've provided my be stopped by the fact that I have multiple years and therefore many of the same month. Additionally, in my results I need it to reference the month in it's own column. So for example, Q3 will add 2 additional months to 1/2017 and therefore will sum(A3,B3,C3). Then R3 would sum(B3,C3,D3), etc.

Can you original solution be modified to accommodate this?