Help Needed Please Formula

[asalman0785]

Hello everyone! Happy Father's Day! Thank you in advance for any help provided.

I've attached a snip example of my problem. I'm trying to build a labor headcount schedule for shifts. On the right (starting on column Y thru AF) i created the actual cohort schedule by day of week (sun-sat).
For example, all cohorts starting with DA will work Sun, Mon, Tue, and Wed which I signified by having an X. These people will be off schedule on Thu, Fri, and Sat which is signified by a blank.

For example, the cohort DA5-0630 has 1 person (column F) so that one person will be working Sun-Wed. How do i get that number 1 across Sun-Wed?

On row 7 i've included the desired answer. For example, I'd like to place the number 1 (because that cohort has 1 person) on column/cell J6-M6.

Hope this helps highlight my problem
Thx

 

[KRice]

Try this:

MrExcel_20220619.xlsx
	A	B	C	D	E	F	G	H	I	J	K													X	Y	Z	AA	AB	AC	AD	AE
1	FC	Cohort	Headcount	Sun	Mon	Tue	Wed	Thu	Fri	Sat														Cohort	Sun	Mon	Tue	Wed	Thu	Fri	Sat
2	ABE2	DA5-0630	1	1	1	1	1																	DA	X	X	X	X
3	ABE2	DA7-630	6	6	6	6	6																	DB				X	X	X	X
4	ABE2	DB1-0630	5				5	5	5	5														DC		X	X		X	X
5	ABE2	DC1-0730	8		8	8		8	8															NA
6																								DN
7																								RT
8																								DF
9																								DL
10																								NL
11																								PT
Sheet1

Cell Formulas
Range	Formula
D2:J5	D2	=IF(INDEX($Y$2:$AE$11,MATCH(LEFT($B2,2),$X$2:$X$11,0),)<>"",$C2,"")
Dynamic array formulas.

 

[asalman0785]

Thanks Krice! Is this possible with Excel 2016 version? I tried control+shift+enter and it didn't work or just gave me data every day of the week. Thankx

 

[KRice]

Try this version, which eliminates the array and performs both a row and column match. Drag the formula throughout your entire table:

MrExcel_20220619.xlsx
	A	B	C	D	E	F	G	H	I	J	K													X	Y	Z	AA	AB	AC	AD	AE
1	FC	Cohort	Headcount	Sun	Mon	Tue	Wed	Thu	Fri	Sat														Cohort	Sun	Mon	Tue	Wed	Thu	Fri	Sat
2	ABE2	DA5-0630	1	1	1	1	1																	DA	X	X	X	X
3	ABE2	DA7-630	6	6	6	6	6																	DB				X	X	X	X
4	ABE2	DB1-0630	5				5	5	5	5														DC		X	X		X	X
5	ABE2	DC1-0730	8		8	8		8	8															NA
6																								DN
7																								RT
8																								DF
9																								DL
10																								NL
11																								PT
Sheet1

Cell Formulas
Range	Formula
D2:J5	D2	=IF(INDEX($Y$2:$AE$11,MATCH(LEFT($B2,2),$X$2:$X$11,0),MATCH(D$1,$Y$1:$AE$1,0))<>"",$C2,"")

 

[asalman0785]

Yes! Beautiful. thank u so much Krice. You saved me!!!

 

[KRice]

That's good to hear. I'm happy to help.