using sumifs function across multiple sheets

[jbrown021286]

i have a sumifs function that i need to to also add cells that match the same criteria from other worksheets

TECH HOURS WORKSHEET 2.0 tester.xlsx
	A	B	C	D	E
1	11-Feb	17-Feb
2
3
4
5
6			101502	101549	103642
7			Jeff	Justin	Ramy
8		hours	24.8	0	37.8
9
10
11
12
13
Hours

Cell Formulas
Range	Formula
A1	A1	=WORKDAY.INTL(B1+1,-1,"1111110")
C8	C8	=SUMIFS('Aggregated Data'!C4:AG4,'Aggregated Data'!C3:AG3,">="&A1,'Aggregated Data'!C3:AG3,"<="&B1)
D8	D8	=SUMIFS('Aggregated Data'!C5:AG5,'Aggregated Data'!C3:AG3,">="&A1,'Aggregated Data'!C3:AG3,"<="&B1)
E8	E8	=SUMIFS('Aggregated Data'!C6:AG6,'Aggregated Data'!C3:AG3,">="&A1,'Aggregated Data'!C3:AG3,"<="&B1)

this is the output of the formula that i am using

TECH HOURS WORKSHEET 2.0 tester.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	AC	AD	AE	AF	AG
1	Tech Hours
2			01FEB24	02FEB24	03FEB24	04FEB24	05FEB24	06FEB24	07FEB24	08FEB24	09FEB24	10FEB24	11FEB24	12FEB24	13FEB24	14FEB24	15FEB24	16FEB24	17FEB24	18FEB24	19FEB24	20FEB24	21FEB24	22FEB24	23FEB24	24FEB24	25FEB24	26FEB24	27FEB24	28FEB24	29FEB24
3			1-Feb	2-Feb	3-Feb	4-Feb	5-Feb	6-Feb	7-Feb	8-Feb	9-Feb	10-Feb	11-Feb	12-Feb	13-Feb	14-Feb	15-Feb	16-Feb	17-Feb	18-Feb	19-Feb	20-Feb	21-Feb	22-Feb	23-Feb	24-Feb	25-Feb	26-Feb	27-Feb	28-Feb	29-Feb
4	Jeff	101502	1.1	9.9	22.5		8.4	22.0	14.2	0.3	17.5	13.9		7.4	8.0	4.6	4.8
5	Justin	101549	2.0	20.0	4.0		6.5	10.4	5.2	5.2	3.0	13.8
6	Ramy	103642	11.2	6.2	3.5			15.1	5.0	9.4	11.6	9.5		1.4	15.8	11.0	9.6
7	Tuzzo	107132	7.1	7.4	8.9		10.5	3.5	3.9	11.2	3.9	5.0		2.3	2.8	8.8	8.0
Aggregated Data

Cell Formulas
Range	Formula
C4:C7	C4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,'Aggregated Data'!B4,RTH!$B:$B,$C$2)
D4:D7	D4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,$D$2)
E4:E7	E4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,$E$2)
F4:F7	F4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,$F$2)
G4:G7	G4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,$G$2)
H4:H7	H4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,H$2)
I4:I7	I4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,I$2)
J4:J7	J4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,J$2)
K4:K7	K4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,K$2)
L4:L7	L4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,L$2)
M4:M7	M4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,M$2)
N4:N7	N4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,N$2)
O4:O7	O4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,O$2)
P4:P7	P4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,P$2)
Q4:Q7	Q4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,Q$2)
R4:R7	R4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,R$2)
S4:S7	S4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,S$2)
T4:T7	T4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,T$2)
U4:U7	U4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,U$2)
V4:V7	V4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,V$2)
W4:W7	W4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,W$2)
X4:X7	X4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,X$2)
Y4:Y7	Y4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,Y$2)
Z4:Z7	Z4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,Z$2)
AA4:AA7	AA4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,AA$2)
AB4:AB7	AB4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,AB$2)
AC4:AC7	AC4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,AC$2)
AD4:AD7	AD4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,AD$2)
AE4:AE7	AE4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,AE$2)
AF4:AF7	AF4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,AF$2)
AG4:AG7	AG4	=SUMIFS(RTH!$N:$N,RTH!$E:$E,B4,RTH!$B:$B,AG$2)

this is the original range that i am adding together but i also need to be able to add from multiple sheets that hold similar data that are just going to be previous months of the data seen here. basically at the end of each month i will be coping the worksheet and pasting it into a new tab for what ever month it was for titling them Jan, Feb, Mar and so on then clearing the data out of this worksheet to start the new month

TECH HOURS WORKSHEET 2.0 tester.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	AC	AD	AE	AF	AG
1	Tech Hours
2			01FEB24	02FEB24	03FEB24	04FEB24	05FEB24	06FEB24	07FEB24	08FEB24	09FEB24	10FEB24	11FEB24	12FEB24	13FEB24	14FEB24	15FEB24	16FEB24	17FEB24	18FEB24	19FEB24	20FEB24	21FEB24	22FEB24	23FEB24	24FEB24	25FEB24	26FEB24	27FEB24	28FEB24	29FEB24
3			1-Feb	2-Feb	3-Feb	4-Feb	5-Feb	6-Feb	7-Feb	8-Feb	9-Feb	10-Feb	11-Feb	12-Feb	13-Feb	14-Feb	15-Feb	16-Feb	17-Feb	18-Feb	19-Feb	20-Feb	21-Feb	22-Feb	23-Feb	24-Feb	25-Feb	26-Feb	27-Feb	28-Feb	29-Feb
4	Jeff	101502	1.1	9.9	22.5	0	8.4	22	14.2	0.3	17.5	13.9	0	7.4	8	4.6	4.8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	Justin	101549	2	20	4	0	6.5	10.4	5.2	5.2	3	13.8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	Ramy	103642	11.2	6.2	3.5	0	0	15.1	5	9.4	11.6	9.5	0	1.4	15.8	11	9.6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Jan

TECH HOURS WORKSHEET 2.0 tester.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	AC	AD	AE	AF	AG
1	Tech Hours
2			01FEB24	02FEB24	03FEB24	04FEB24	05FEB24	06FEB24	07FEB24	08FEB24	09FEB24	10FEB24	11FEB24	12FEB24	13FEB24	14FEB24	15FEB24	16FEB24	17FEB24	18FEB24	19FEB24	20FEB24	21FEB24	22FEB24	23FEB24	24FEB24	25FEB24	26FEB24	27FEB24	28FEB24	29FEB24
3			1-Feb	2-Feb	3-Feb	4-Feb	5-Feb	6-Feb	7-Feb	8-Feb	9-Feb	10-Feb	11-Feb	12-Feb	13-Feb	14-Feb	15-Feb	16-Feb	17-Feb	18-Feb	19-Feb	20-Feb	21-Feb	22-Feb	23-Feb	24-Feb	25-Feb	26-Feb	27-Feb	28-Feb	29-Feb
4	Jeff	101502	1.1	9.9	22.5	0	8.4	22	14.2	0.3	17.5	13.9	0	7.4	8	4.6	4.8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	Justin	101549	2	20	4	0	6.5	10.4	5.2	5.2	3	13.8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	Ramy	103642	11.2	6.2	3.5	0	0	15.1	5	9.4	11.6	9.5	0	1.4	15.8	11	9.6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Feb

TECH HOURS WORKSHEET 2.0 tester.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	AC	AD	AE	AF	AG
1	Tech Hours
2			01FEB24	02FEB24	03FEB24	04FEB24	05FEB24	06FEB24	07FEB24	08FEB24	09FEB24	10FEB24	11FEB24	12FEB24	13FEB24	14FEB24	15FEB24	16FEB24	17FEB24	18FEB24	19FEB24	20FEB24	21FEB24	22FEB24	23FEB24	24FEB24	25FEB24	26FEB24	27FEB24	28FEB24	29FEB24
3			1-Feb	2-Feb	3-Feb	4-Feb	5-Feb	6-Feb	7-Feb	8-Feb	9-Feb	10-Feb	11-Feb	12-Feb	13-Feb	14-Feb	15-Feb	16-Feb	17-Feb	18-Feb	19-Feb	20-Feb	21-Feb	22-Feb	23-Feb	24-Feb	25-Feb	26-Feb	27-Feb	28-Feb	29-Feb
4	Jeff	101502	1.1	9.9	22.5	0	8.4	22	14.2	0.3	17.5	13.9	0	7.4	8	4.6	4.8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	Justin	101549	2	20	4	0	6.5	10.4	5.2	5.2	3	13.8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	Ramy	103642	11.2	6.2	3.5	0	0	15.1	5	9.4	11.6	9.5	0	1.4	15.8	11	9.6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Mar

for the purposes of the example i have just copied the data from my "aggregated Data" sheet and made copies if it to mock up months of jan, feb, and mar. any help would be appreciated.

 

[jbrown021286]

TECH HOURS WORKSHEET 2.0 tester.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	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1	11-Feb	17-Feb
2
3										Week to Date																											Jan
4																																					Feb
5																																					Mar
6			101502	101549	103642	107132	107471	107801	108228	108243	108410	108634	109543	109572	109783	109930	1021024	1021025	1021026	1021027	1021028	1021029	1021032	1021037	1021038	1021039	1021040										Apr
7			Jeff	Justin	Ramy	Tuzzo	Brandun	Carl	Rod Jones	Que	James	Owens	Hawk	Josh	David	Angel	Immanuel	Jamie	Peter	Frederick	Jerusha	Gerrardo	Tre	Harry	Contrer	Trevin	Venro										May
8		hours	24.8	0	#REF!																																Jun
9																																					Jul
10																																					Aug
11																																					Sep
12																																					Oct
13										year to Date																											Nov
14																																					Dec
Hours

Cell Formulas
Range	Formula
A1	A1	=WORKDAY.INTL(B1+1,-1,"1111110")
C8	C8	=SUMIFS('Aggregated Data'!C4:AG4,'Aggregated Data'!C3:AG3,">="&A1,'Aggregated Data'!C3:AG3,"<="&B1)
D8	D8	=SUMIFS('Aggregated Data'!C5:AG5,'Aggregated Data'!C3:AG3,">="&A1,'Aggregated Data'!C3:AG3,"<="&B1)
E8	E8	=SUMPRODUCT(SUMIFS(INDIRECT("'"&$AK$3:$AK$14&"'!C4:AG4"),(INDIRECT("'"&$AK$3:$AK$14&"'!C3:AG3,">="&A1")),(INDIRECT("'"&$AK$3:$AK$14&"'!C3:AG3,"<="&B1"))))

i did try this formula im not sure if i have an error in it or if it cant be used like this =SUMPRODUCT(SUMIFS(INDIRECT("'"&$AK$3:$AK$14&"'!C4:AG4"),(INDIRECT("'"&$AK$3:$AK$14&"'!C3:AG3,">="&A1")),(INDIRECT("'"&$AK$3:$AK$14&"'!C3:AG3,"<="&B1"))))

 

[KRice]

What are you trying to do? Your three monthly mock-up worksheets are identical, except for the sheet names. Jan, Feb, and Mar all use the same dates in February, same names, same hours. And none of those sheets are referenced in your Aggregated Date worksheet. There, formulas refer to an RTH worksheet that is not described. It would be helpful to explain what you expect to be do every month. Are you trying to aggregate hours from certain months?...which ones?...how would those months be specified?

 

[jbrown021286]

What are you trying to do? Your three monthly mock-up worksheets are identical, except for the sheet names. Jan, Feb, and Mar all use the same dates in February, same names, same hours. And none of those sheets are referenced in your Aggregated Date worksheet. There, formulas refer to an RTH worksheet that is not described. It would be helpful to explain what you expect to be do every month. Are you trying to aggregate hours from certain months?...which ones?...how would those months be specified?

I just copied the month of Feb since it was the only data collected to this point. As the months end the sheet will be copied to the corisonding month for reference purposes. The rth sheet that is referenced is the raw data report that is input into my spreadsheet every day and updates the cells in the aggregated data sheet accordingly. The months and days would be indicated by row 3 on each sheet in a normal.date format. Ultimately my sumifs function needs to be able to sum the numbers from each name in the list starting with the Date in a1 and ending in the date b1 across the aggregated data, jan, feb, mar, apr, may, Jun, Jul, Aug, sep, Oct, Nov, and Dec sheets

 

[KRice]

Ultimately my sumifs function needs to be able to sum the numbers from each name in the list starting with the Date in a1 and ending in the date b1 across the aggregated data, jan, feb, mar, apr, may, Jun, Jul, Aug, sep, Oct, Nov, and Dec sheets

I'm still puzzled. I'm assuming the reference to A1 and B1 means the the cells on worksheet Hours shown in post #1? But those cells are set up to receive an input from the user in B1, and then the formula in A1 finds the date of the prior Sunday. So this represents, at most, a date range of only one week. Is the formula in A1 only temporary, and at times you might input a different date, or modify the formula to go back further in time?

I just copied the month of Feb since it was the only data collected to this point.

My point about Jan, Feb, and Mar is that--to the reader--we do not know what you know about your data or your worksheets. I would have expected a Jan worksheet to show calendar dates in the month of January, along with fictitious data...data that would be different from February's. That way, when testing formulas, it is easy to confirm that data are being pulled from the correct worksheets. As it stands now, you can't pull data from the Jan or Mar worksheets without specifying dates in February, unless you rely solely on looking up cell addresses, rather than relying on the dates in rows 2 or 3 of the monthly sheets.

As the months end the sheet will be copied to the corisonding month for reference purposes.

I'm confused...where are data initially recorded? I assumed that during January, daily data were recorded on the Jan worksheet, and when January ends, the Jan worksheet is archived and you move to a new worksheet named "Feb". But now the mystery of the RTH worksheet is becoming clearer. Are you saying that you always use the RTH worksheet for daily recording, regardless of the month...and you collect one month of data on the RTH worksheet, copy all of that data to an archived worksheet named for the month (Jan, Feb, Mar, etc.), then clear the data block on the RTH worksheet and prepare it for the next month?

Do you ever need to sum over a date range that crosses from one calendar year to another, say December 2023 through February 2024?

Based on what I am understanding at the moment, I would seriously consider changing the archiving process, and perhaps use Power Query to transform each month's input table on the RTH worksheet (assuming that is correct) into a flat table represented by just 4 columns: Name, col B code (whatever that represents), Date, Hours...and that transformed monthly table would be appended to an accumulating table that has all prior months' data in it, creating a single historical archive. That table would grow in length, approximately 30 * number of staff each month. The table, while not very easy for us to look at and understand, is very well suited for use by Excel formulas. It can be readily filtered to extract data by person, date/date range,..and the question you are asking about summing over certain months could be done without having to either hardcode multiple worksheet names into formulas or rely on INDIRECT functions. But let me ask first, about how large are these monthly tables...how many people (rows) are being logged each month?