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

Model	Region	Calculation
Model 1	Region 1	(expected result 140 from sheet 2)

<tbody>
</tbody>

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

Sheet2

Assuming the date starts in A1

Model	Region	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sept-19	Oct-19	Nov-19	Dec-19
Model 1	Region 1	5	3	4	6	9	16	15	20	23	22	9	8	1

<tbody>
</tbody>

 

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