Sumproduct, Sum on running date

[Cferron]

Hello community,

I would like to dynamically sum a rage of cells based on a start date.
The start date is basically =Today()
End date is today + 6

These SUM would be summarize in a table. I have use the SUM to crate the "base" table, but again, I would like it to be dynamic based on the date.

I was thinking about a SUMPRODUCT using an array, but I'm not to familiar with programming those.

Any ideas welcome!

Quarts-test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1					Dates
2	Régions	Villes	Secteurs	Uniquename	Hlookup	10/5/22	11/6/22	11/7/22	11/8/22	11/9/22	11/10/22	11/11/22	11/12/22	11/13/22	11/14/22	11/15/22
3	SUD	Victo	SNT COV	SUDVictoSNT COV	2	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A
4	SUD	Victo	Brigade COV 5-11 ans	SUDVictoBrigade COV 5-11 ans	3	2	2	2	2	2	2	2	2	2	2	2
5	SUD	Victo	Mobile COVID	SUDVictoMobile COVID	4	1	1	1	1	1	1	1	1	1	1	1
6	SUD	Victo	SAD	SUDVictoSAD	5	0	0	0	0	0	0	0	0	0	0	0
7	SUD	Victo	CDD	SUDVictoCDD	6	0	0	0	0	0	0	0	0	0	0	0
8	SUD	Victo	CDD Mobile	SUDVictoCDD Mobile	7	0	0	0	0	0	0	0	0	0	0	0
9	SUD	Fortier	SNT COV	SUDFortierSNT COV	8	0	0	0	0	0	0	0	0	0	0	0
10	SUD	Fortier	Brigade COV 5-11 ans	SUDFortierBrigade COV 5-11 ans	9	0	0	0	0	0	0	0	0	0	0	0
11	SUD	Fortier	Mobile COVID	SUDFortierMobile COVID	10	0	0	0	0	0	0	0	0	0	0	0
12	SUD	Fortier	SAD	SUDFortierSAD	11	0	0	0	0	0	0	0	0	0	0	0
13	SUD	Fortier	CDD	SUDFortierCDD	12	0	0	0	0	0	0	0	0	0	0	0
14	SUD	Fortier	CDD Mobile	SUDFortierCDD Mobile	13	0	0	0	0	0	0	0	0	0	0	0
15	SUD	Drummond	SNT COV	SUDDrummondSNT COV	14	0	0	0	0	0	0	0	0	0	0	0				Total 7 jours
16	SUD	Drummond	Brigade COV 5-11 ans	SUDDrummondBrigade COV 5-11 ans	15	0	0	0	0	0	0	0	0	0	0	0				Du	10/5/22	au	10/11/22
17	SUD	Drummond	Mobile COVID	SUDDrummondMobile COVID	16	0	0	0	0	0	0	0	0	0	0	0
18	SUD	Drummond	SAD	SUDDrummondSAD	17	0	0	0	0	0	0	0	0	0	0	0			Secteurs	Victo	Fortierville	DR	La Tuque	St-Tite	Shawi	3R	BNY	Louiseville
19	SUD	Drummond	CDD	SUDDrummondCDD	18	0	0	0	0	0	0	0	0	0	0	0			SNT COV	0	0	0	0	0	0	700	7	7
20	SUD	Drummond	CDD Mobile	SUDDrummondCDD Mobile	19	100	100	100	100	100	100	100	100	100	100	100			Brigade COV 5-11 ans	14	0	0	14	0	0	7	7	7
21	NORD	La Tuque	SNT COV	NORDLa TuqueSNT COV	20	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A			Mobile COVID	7	0	0	7	0	0	7	7	7
22	NORD	La Tuque	Brigade COV 5-11 ans	NORDLa TuqueBrigade COV 5-11 ans	21	2	2	2	2	2	2	2	2	2	2	2			SAD	0	0	0	0	0	0	7	7	7
23	NORD	La Tuque	Mobile COVID	NORDLa TuqueMobile COVID	22	1	1	1	1	1	1	1	1	1	1	1			CDD	0	0	0	0	0	0	7	7	7
24	NORD	La Tuque	SAD	NORDLa TuqueSAD	23	0	0	0	0	0	0	0	0	0	0	0			CDD Mobile	0	0	700	0	0	700	7	7	7
25	NORD	La Tuque	CDD	NORDLa TuqueCDD	24	0	0	0	0	0	0	0	0	0	0	0
26	NORD	La Tuque	CDD Mobile	NORDLa TuqueCDD Mobile	25	0	0	0	0	0	0	0	0	0	0	0
27	NORD	St-Tite	SNT COV	NORDSt-TiteSNT COV	26	0	0	0	0	0	0	0	0	0	0	0
28	NORD	St-Tite	Brigade COV 5-11 ans	NORDSt-TiteBrigade COV 5-11 ans	27	0	0	0	0	0	0	0	0	0	0	0
29	NORD	St-Tite	Mobile COVID	NORDSt-TiteMobile COVID	28	0	0	0	0	0	0	0	0	0	0	0
30	NORD	St-Tite	SAD	NORDSt-TiteSAD	29	0	0	0	0	0	0	0	0	0	0	0
31	NORD	St-Tite	CDD	NORDSt-TiteCDD	30	0	0	0	0	0	0	0	0	0	0	0
32	NORD	St-Tite	CDD Mobile	NORDSt-TiteCDD Mobile	31	0	0	0	0	0	0	0	0	0	0	0
33	NORD	Shawi	SNT COV	NORDShawiSNT COV	32	0	0	0	0	0	0	0	0	0	0	0
34	NORD	Shawi	Brigade COV 5-11 ans	NORDShawiBrigade COV 5-11 ans	33	0	0	0	0	0	0	0	0	0	0	0
35	NORD	Shawi	Mobile COVID	NORDShawiMobile COVID	34	0	0	0	0	0	0	0	0	0	0	0
36	NORD	Shawi	SAD	NORDShawiSAD	35	0	0	0	0	0	0	0	0	0	0	0
37	NORD	Shawi	CDD	NORDShawiCDD	36	0	0	0	0	0	0	0	0	0	0	0
38	NORD	Shawi	CDD Mobile	NORDShawiCDD Mobile	37	100	100	100	100	100	100	100	100	100	100	100
39	CENTRE	3R	SNT COV	CENTRE3RSNT COV	38	100	100	100	100	100	100	100	100	100	100	100
40	CENTRE	3R	Brigade COV 5-11 ans	CENTRE3RBrigade COV 5-11 ans	39	1	1	1	1	1	1	1	1	1	1	1
41	CENTRE	3R	Mobile COVID	CENTRE3RMobile COVID	40	1	1	1	1	1	1	1	1	1	1	1
42	CENTRE	3R	SAD	CENTRE3RSAD	41	1	1	1	1	1	1	1	1	1	1	1
43	CENTRE	3R	CDD	CENTRE3RCDD	42	1	1	1	1	1	1	1	1	1	1	1
44	CENTRE	3R	CDD Mobile	CENTRE3RCDD Mobile	43	1	1	1	1	1	1	1	1	1	1	1
45	CENTRE	BNY	SNT COV	CENTREBNYSNT COV	44	1	1	1	1	1	1	1	1	1	1	1
46	CENTRE	BNY	Brigade COV 5-11 ans	CENTREBNYBrigade COV 5-11 ans	45	1	1	1	1	1	1	1	1	1	1	1
47	CENTRE	BNY	Mobile COVID	CENTREBNYMobile COVID	46	1	1	1	1	1	1	1	1	1	1	1
48	CENTRE	BNY	SAD	CENTREBNYSAD	47	1	1	1	1	1	1	1	1	1	1	1
49	CENTRE	BNY	CDD	CENTREBNYCDD	48	1	1	1	1	1	1	1	1	1	1	1
50	CENTRE	BNY	CDD Mobile	CENTREBNYCDD Mobile	49	1	1	1	1	1	1	1	1	1	1	1
51	CENTRE	Louiseville	SNT COV	CENTRELouisevilleSNT COV	50	1	1	1	1	1	1	1	1	1	1	1
52	CENTRE	Louiseville	Brigade COV 5-11 ans	CENTRELouisevilleBrigade COV 5-11 ans	51	1	1	1	1	1	1	1	1	1	1	1
53	CENTRE	Louiseville	Mobile COVID	CENTRELouisevilleMobile COVID	52	1	1	1	1	1	1	1	1	1	1	1
54	CENTRE	Louiseville	SAD	CENTRELouisevilleSAD	53	1	1	1	1	1	1	1	1	1	1	1
55	CENTRE	Louiseville	CDD	CENTRELouisevilleCDD	54	1	1	1	1	1	1	1	1	1	1	1
56	CENTRE	Louiseville	CDD Mobile	CENTRELouisevilleCDD Mobile	55	1	1	1	1	1	1	1	1	1	1	1
Feuil1

Cell Formulas
Range	Formula
U16	U16	=TODAY()
W16	W16	=U16+6
T19:T24	T19	=SUM(F3:L3)
U19:U24	U19	=SUM(F9:L9)
V19:V24	V19	=SUM(F15:L15)
W19:W24	W19	=SUM(F21:L21)
X19:X24	X19	=SUM(F27:L27)
Y19:Y24	Y19	=SUM(F33:L33)
Z19:Z24	Z19	=SUM(F39:L39)
AA19:AA24	AA19	=SUM(F45:L45)
AB19:AB24	AB19	=SUM(F51:L51)
D3:D56	D3	=CONCAT(A3,B3,C3)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Cferron]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Just did that.
I have access to several version or platform.
Excel Online (both mac and PC) - (English environnement)
Excel 2019 PC (French canadian environnement)
Excel 16.6 Mac (French canadian environnement)

 

[Fluff]

Which does this need to work with?

 

[Fluff]

How about

=SUMPRODUCT(($F$2:$P$2>=$U$16)*($F$2:$P$2<=$W$16),F3:P3)

 

[Cferron]

It is working perfectly! Thanks a million!

 

[Cferron]

How about

=SUMPRODUCT(($F$2:$P$2>=$U$16)*($F$2:$P$2<=$W$16),F3:P3)

How about

=SUMPRODUCT(($F$2:$P$2>=$U$16)*($F$2:$P$2<=$W$16),F3:P3)

It is working perfectly! Thanks a million!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Cferron]

I can't seem to be able to delete my post.
Initially I thought it was not working, but I noticed I had an issue with my dates in the main table.

Thanks again.