Formula to pull [Array?] totals from all columns that say "Reg" at the top?

[Domroy]

I have a workbook I'm trying to build - it has multiple project worksheets that have a breakdown of weeks (6/13-6/19, 6/20-6/26, etc) in column A. Underneath each week is a list of names. along the top there are projects, and how many hours someone puts in as "Reg" hours or "OT" hours (which correspond to each name in column A). This is all duplicated for every week because I need to have a place to enter the number of hours worked on each part of the project. I've uploaded a picture of my sheet. In any case, in the weekly total columns (AF and AG), I'd like to put in a formula that will look across the whole row for each person, and total each person's regular hours and overtime hours. I know there's a better way than to have to type in the formula like =sum(b6,d6,h6,k6...etc. That's my first issue.

Then, I have another sheet that looks for the regular and OT totals for each person on each project spreadsheet, and rolls that up (as each person may work on more than one project each week) as a payroll tab so that we know how many hours total each week that person has worked.

Can someone help me? I feel like this may be [very complicated, to me] array formulas. I'm good with formulas, but not array ones, and I feel like building this with array formulas may make my life SO MUCH EASIER, and save me so much time and remove the error percentage. UGH. It's so much.

Thanks in advance!!

Judi

 

[awoohaw]

You can help people help you by posting good representative samples of your data, (anonymized) using the xl2bb tool. And folks also need to know what version of excel your use?

 

[Domroy]

AF hours 2023.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
1	Project Name
2		Drape		Pattern		Cut		Stitch		Fit/Alter/Mod		Document		2nd Set Support		Extra		Extra		Extra		Extra		Extra		Extra		Extra		Extra		Weekly Totals
3
4		Reg	OT	Reg	OT	Reg	OT	Reg	OT	Reg	OT	Reg	OT	Reg	OT	Reg	OT	Reg	OT	Reg	OT	Reg	OT	Reg	OT	Reg	OT	Reg	OT	Reg	OT	Reg	OT
5	6/13-6/19
6	Randy																															0	0
7	Judi																															0	0
8	Sam																															0	0
9	Danny																															0	0
10	Ryan																															0	0
11	Kyra																															0	0
12	TOTAL	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	6/20-6/26
14	Randy																															0	0
15	Judi																															0	0
16	Sam																															0	0
17	Danny																															0	0
18	Ryan																															0	0
19	Kyra																															0	0
20	TOTAL	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Project Template

Cell Formulas
Range	Formula
AF6:AF11,AF14:AF19	AF6	=SUM(B6,D6,F6,H6,J6,L6,N6,P6,R6,T6)
AG6:AG11,AG14:AG19	AG6	=SUM(C6,E6,G6,I6,K6,M6,O6,Q6,S6, U6)
B12:AG12,B20:AG20	B12	=SUM(B6:B11)
AH12,AH20	AH12	=SUM(AF12:AG12)

 

[Domroy]

Figured it out! Thank you!!!