sumifs with dynamic suming range

[TG2812]

Hi, I'm trying to come up with a dynamic suming range within sumifs formula.
I'm trying to sum between today and today +12 with several conditions.


How can I get to the below result with a SUMIFS? What I'm struggling with is to come up with a dynamic summing range. Most probably with OFFSET?

Sheet1

Assuming the data starts in A1

[TABLE="width: 500"]
<tbody>[TR]
[TD]Model[/TD]
[TD]Region[/TD]
[TD]Calculation[/TD]
[/TR]
[TR]
[TD]Model 1[/TD]
[TD]Region 1[/TD]
[TD](expected result 140 from sheet 2)[/TD]
[/TR]
</tbody>[/TABLE]

The expected result is the sum between Dec-18 and Nov-19 = 140

Sheet2

Assuming the date starts in A1

[TABLE="width: 500"]
<tbody>[TR]
[TD]Model[/TD]
[TD]Region[/TD]
[TD]Dec-18[/TD]
[TD]Jan-19[/TD]
[TD]Feb-19[/TD]
[TD]Mar-19[/TD]
[TD]Apr-19[/TD]
[TD]May-19[/TD]
[TD]Jun-19[/TD]
[TD]Jul-19[/TD]
[TD]Aug-19[/TD]
[TD]Sept-19[/TD]
[TD]Oct-19[/TD]
[TD]Nov-19[/TD]
[TD]Dec-19[/TD]
[/TR]
[TR]
[TD]Model 1[/TD]
[TD]Region 1[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]16[/TD]
[TD]15[/TD]
[TD]20[/TD]
[TD]23[/TD]
[TD]22[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about
=SUMIFS(C2:O2,C1:O1,">="&EOMONTH(TODAY(),-1)+1,C1:O1,"<="&EOMONTH(TODAY(),11))

 

[TG2812]

Hi, unfortunately the below formula returns me a #VALUE error:
=SUMIFS(Sheet2!$E$3:$Z$10000,Sheet2!$2:$2,">="&EOMONTH(TODAY(),-1)+1,Sheet2!$2:$2,">="&EOMONTH(TODAY(),11),Sheet2!$A:$A,Sheet1!A1,Sheet2!$B:$B,Sheet1!B1)

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Model	Region	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19
2	Model 1	Region 2	5	3	4	6	9	20	30	20	23	22	9	8	1
3	Model 1	Region 1	5	3	4	6	9	16	15	20	23	22	9	8	1
4
5
6	Model	Region	Calculation
7	Model 1	Region 1	140
search

Cell Formulas
Range	Formula
C7	{=SUMIFS(INDEX(C2:O3,MATCH(1,(A2:A3=A7)*(B2:B3=B7),0),0),C1:O1,">="&EOMONTH(TODAY(),-1)+1,C1:O1,"<="&EOMONTH(TODAY(),11))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Do not use whole row/column references with this formula.

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Model	Region	1-Dec-18	1-Jan-19	1-Feb-19	1-Mar-19	1-Apr-19	1-May-19	1-Jun-19	1-Jul-19	1-Aug-19	1-Sep-19	1-Oct-19	1-Nov-19	1-Dec-19
2	Model 1	Region 2	5	3	4	6	9	20	30	20	23	22	9	8	1
3	Model 1	Region 1	5	3	4	6	9	16	15	20	23	22	9	8	1
Sheet2

Book1
	A	B	C
1	1-Dec-18
2	1-Nov-19
3	Model	Region	Calculation
4	Model 1	Region 1	140
5
Sheet1

In c4 control+shift+enter, not just enter:

=SUMIFS(INDEX(Sheet2!$C$2:$O$3,MATCH($A4,IF(Sheet2!$B$2:$B$3=$B4,Sheet2!$A$2:$A$3),0),0),Sheet2!$C$1:$O$1,">="&$A$1,Sheet2!$C$1:$O$1,"<="&$A$2)

 

[TG2812]

Thanks. Do you have a way to do it without array formula? The file I'm working on is already very heavy..

 

[Fluff]

I don't know of any way of doing it without an array formula, but formulae aren't my strong point.

 

[Aladin Akyurek]

Thanks. Do you have a way to do it without array formula? The file I'm working on is already very heavy..

The following set up allows you to invoke range-processing which faster than an equivalent array processing...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Model	Region	CONCAT	1-Dec-18	1-Jan-19	1-Feb-19	1-Mar-19	1-Apr-19	1-May-19	1-Jun-19	1-Jul-19	1-Aug-19	1-Sep-19	1-Oct-19	1-Nov-19	1-Dec-19
2	Model 1	Region 2	Model 1|Region 2	5	3	4	6	9	20	30	20	23	22	9	8	1
3	Model 1	Region 1	Model 1|Region 1	5	3	4	6	9	16	15	20	23	22	9	8	1
Sheet2

In C2 of Sheet2 just enter and copy down while the data is shifted one column to the right...

=A2&"|"&B2

Book1
	A	B	C
1	1-Dec-18
2	1-Nov-19
3	Model	Region	Calculation
4	Model 1	Region 1	140
5
Sheet1

The formula in C4 becomes:

=SUMIFS(INDEX(Sheet2!$D$2:$P$3,MATCH($A4&"|"&$B4,Sheet2!$C$2:$C$3,0),0),Sheet2!$D$1:$P$1,">="&$A$1,Sheet2!$D$1:$P$1,"<="&$A$2)

which needs just enter.