COUNTIF+INDEX+MATCH formula

[MZING81]

I seemed to hit a wall. I cannot for life of me figure out how to properly combine an countif, index/match all together. I have 4 criteria one of which is a header. Ultimately I'm trying to find out how many employees for a specific location on specific date, shift are going to be where. Below is the original formula not necessarily for the dummy sheet below.

=SUMPRODUCT(--(final!$G$9:$G$1200&final!$K$9:$K$1200=$G$5&H5),INDEX(final!$AA$8:$AG$1200,,MATCH(H4&G6,final!$AA$8:$AG$8&$AA$9:$AG$1200,0)))

I also tried

=SUMIF(final!$BC$9:$BC$1200,$G$5&H5,INDEX(final!$AA$9:$AG$1200,,MATCH(H4&G6,final!$AA$8:$AG$8&final!$AA$9:$AG$1200,0)))

and
=COUNTIF(INDEX(final!$AA$9:$AG$1300,0,MATCH(H$5,final!$K$9:$K$1300,0)),$G8)-COUNTIF(INDEX(final!$AA$9:$AG$1300,0,MATCH(H$4,final!$AA$8:$AG$8,0)),$G8)

But nothing seemed to work properly.

Any help is greatly appreciated. I apologize if this is a remotely similar to something I posted a while ago but got no responses.
[TABLE="width: 853"]
<colgroup><col width="132" style="width: 99pt; mso-width-source: userset; mso-width-alt: 4827;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <col width="131" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;" span="7"> <tbody>[TR]
[TD="width: 132, bgcolor: transparent"][/TD]
[TD="width: 109, bgcolor: transparent"][/TD]
[TD="class: xl243, width: 131, bgcolor: transparent"]DATE[/TD]
[TD="class: xl243, width: 109, bgcolor: transparent"]SUNDAY[/TD]
[TD="class: xl243, width: 109, bgcolor: transparent"]MONDAY[/TD]
[TD="class: xl243, width: 109, bgcolor: transparent"]TUESDAY[/TD]
[TD="class: xl243, width: 109, bgcolor: transparent"]WEDNESDAY[/TD]
[TD="class: xl243, width: 109, bgcolor: transparent"]THURSDAY[/TD]
[TD="class: xl243, width: 109, bgcolor: transparent"]FRIDAY[/TD]
[TD="class: xl243, width: 109, bgcolor: transparent"]SATURDAY[/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 1[/TD]
[TD="class: xl241, bgcolor: transparent"]AM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/2/2017[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-4[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-4[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-5[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-5[/TD]
[TD="class: xl240, bgcolor: transparent"]KIOS-4[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 2[/TD]
[TD="class: xl241, bgcolor: transparent"]AM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/3/2017[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 3[/TD]
[TD="class: xl241, bgcolor: transparent"]AM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/4/2017[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 4[/TD]
[TD="class: xl241, bgcolor: transparent"]AM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/5/2017[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-1[/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 5[/TD]
[TD="class: xl241, bgcolor: transparent"]PM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/6/2017[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-0[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-0[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-0[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-0[/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 6[/TD]
[TD="class: xl241, bgcolor: transparent"]PM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/7/2017[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl240, bgcolor: transparent"]KIOS-1[/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 7[/TD]
[TD="class: xl241, bgcolor: transparent"]PM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/2/2017[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-2[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-2[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-2[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 8[/TD]
[TD="class: xl241, bgcolor: transparent"]AM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/3/2017[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-3[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-3[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-3[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-3[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-3[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 9[/TD]
[TD="class: xl241, bgcolor: transparent"]AM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/4/2017[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-5[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-5[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-4[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-5[/TD]
[TD="class: xl240, bgcolor: transparent"]OFFSITE[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 10[/TD]
[TD="class: xl241, bgcolor: transparent"]AM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/5/2017[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-5[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-5[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-5[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-5[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-5[/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 11[/TD]
[TD="class: xl241, bgcolor: transparent"]PM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/6/2017[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 12[/TD]
[TD="class: xl241, bgcolor: transparent"]PM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/7/2017[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-2[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-2[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-2[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-2[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-2[/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 13[/TD]
[TD="class: xl241, bgcolor: transparent"]PM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/2/2017[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-2[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl240, bgcolor: transparent"]OFFSITE[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 14[/TD]
[TD="class: xl241, bgcolor: transparent"]AM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/3/2017[/TD]
[TD="class: xl242, bgcolor: transparent"]OFFSITE[/TD]
[TD="class: xl242, bgcolor: transparent"]OFFSITE[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-2[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"]KIOS-1[/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 15[/TD]
[TD="class: xl241, bgcolor: transparent"]AM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/4/2017[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl242, bgcolor: transparent"]OFFSITE[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-2[/TD]
[TD="class: xl240, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl240, bgcolor: transparent"]KIOS-1[/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 16[/TD]
[TD="class: xl241, bgcolor: transparent"]AM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/5/2017[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"]WAREHOUSE[/TD]
[TD="class: xl242, bgcolor: transparent"]WAREHOUSE[/TD]
[TD="class: xl242, bgcolor: transparent"]WAREHOUSE[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 17[/TD]
[TD="class: xl241, bgcolor: transparent"]AM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/6/2017[/TD]
[TD="class: xl242, bgcolor: transparent"]WAREHOUSE[/TD]
[TD="class: xl242, bgcolor: transparent"]WAREHOUSE[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-5[/TD]
[TD="class: xl242, bgcolor: transparent"]WAREHOUSE[/TD]
[TD="class: xl240, bgcolor: transparent"]WAREHOUSE[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 18[/TD]
[TD="class: xl241, bgcolor: transparent"]PM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/7/2017[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-1[/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 19[/TD]
[TD="class: xl241, bgcolor: transparent"]PM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/2/2017[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl240, bgcolor: transparent"]KIOS-1[/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 20[/TD]
[TD="class: xl241, bgcolor: transparent"]PM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/3/2017[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-2[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-2[/TD]
[TD="class: xl240, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 21[/TD]
[TD="class: xl241, bgcolor: transparent"]AM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/4/2017[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 22[/TD]
[TD="class: xl241, bgcolor: transparent"]AM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/5/2017[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 23[/TD]
[TD="class: xl241, bgcolor: transparent"]PM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/6/2017[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 24[/TD]
[TD="class: xl241, bgcolor: transparent"]PM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/7/2017[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-0[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-4[/TD]
[TD="class: xl242, bgcolor: transparent"]KIOS-4[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-0[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-0[/TD]
[TD="class: xl240, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl241, bgcolor: transparent"]EMPLOYEE 25[/TD]
[TD="class: xl241, bgcolor: transparent"]AM[/TD]
[TD="class: xl244, bgcolor: transparent"]1/2/2017[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-5[/TD]
[TD="class: xl242, bgcolor: transparent"]STORE-5[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-5[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-5[/TD]
[TD="class: xl240, bgcolor: transparent"]STORE-5[/TD]
[/TR]
</tbody>[/TABLE]

This is where I would like to fill the data
[TABLE="width: 672"]
<colgroup><col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <col width="131" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;" span="6"> <tbody>[TR]
[TD="width: 109, bgcolor: transparent"][/TD]
[TD="class: xl244, width: 131, bgcolor: transparent"]MONDAY[/TD]
[TD="class: xl244, width: 109, bgcolor: transparent"]TUESDAY[/TD]
[TD="class: xl244, width: 109, bgcolor: transparent"]WEDNESDAY[/TD]
[TD="class: xl244, width: 109, bgcolor: transparent"]THURSDAY[/TD]
[TD="class: xl244, width: 109, bgcolor: transparent"]FRIDAY[/TD]
[TD="class: xl244, width: 109, bgcolor: transparent"]SATURDAY[/TD]
[TD="class: xl244, width: 109, bgcolor: transparent"]SUNDAY[/TD]
[/TR]
[TR]
[TD="class: xl242, bgcolor: transparent"]AM[/TD]
[TD="class: xl245, bgcolor: transparent"]1/2/2017[/TD]
[TD="class: xl245, bgcolor: transparent"]1/3/2017[/TD]
[TD="class: xl245, bgcolor: transparent"]1/4/2017[/TD]
[TD="class: xl245, bgcolor: transparent"]1/5/2017[/TD]
[TD="class: xl245, bgcolor: transparent"]1/6/2017[/TD]
[TD="class: xl245, bgcolor: transparent"]1/7/2017[/TD]
[TD="class: xl245, bgcolor: transparent"]1/8/2017[/TD]
[/TR]
[TR]
[TD="class: xl242, bgcolor: transparent"]STORE-1[/TD]
[TD="class: xl242, bgcolor: transparent"] ??[/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl242, bgcolor: transparent"]STORE-2[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl242, bgcolor: transparent"]STORE-3[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl242, bgcolor: transparent"]WAREHOUSE[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl242, bgcolor: transparent"]KIOS-1[/TD]
[TD="class: xl242, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[TD="class: xl244, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

Thanks
Mzing81

 

[Eric W]

How does your first table work? For example, on the first row, Employee 1 has a date of 1/2/2017, and then going to the right there are 7 days. Does the location in the boxes going to the right represent where he'll be on Sunday 1/1, Monday 1/2, Tuesday 1/3, etc.? Employee 2 has a date of 1/3/2017, how does that relate to the days of the week?

 

[MZING81]

The Sunday thru Saturday header/table area represents their regular schedule. This is part of a larger workbook/generator for 1500 employees and the second portion is more of a dashboard that's on a separate sheet. The dates are represented for training that the employees need to attend and the dashboard below is to identify how many employees are on training from each given location on each day for each shift to view the staffing impact. The dates will regularly be changed.

 

[WarfritLive]

I don't understand why you're using Index. If it were me, the question mark cell would say
=SUMPRODUCT(--('Sheet 2'!E:E=$A3)), but I almost definitely don't understand what kind of outputs you're looking for. What are the dates in column C even for? What criteria do need outside of counting employees at a given store on xyz day of the week

Edit:E:E for Monday.

 

[Eric W]

How about this?

	A	B	C	D	E	F	G	H	I	J
MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY
AM
STORE-1
STORE-2
STORE-3
STORE-4
STORE-5
KIOS-1
KIOS-2
KIOS-3
KIOS-4
WAREHOUSE
OFFSITE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]DATE[/TD]
[TD="bgcolor: #FAFAFA"]SUNDAY[/TD]
[TD="bgcolor: #FAFAFA"]MONDAY[/TD]
[TD="bgcolor: #FAFAFA"]TUESDAY[/TD]
[TD="bgcolor: #FAFAFA"]WEDNESDAY[/TD]
[TD="bgcolor: #FAFAFA"]THURSDAY[/TD]
[TD="bgcolor: #FAFAFA"]FRIDAY[/TD]
[TD="bgcolor: #FAFAFA"]SATURDAY[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 1[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/2/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 2[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/3/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 3[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/4/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 4[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/5/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 5[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/6/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 6[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/7/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 7[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/2/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 8[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/3/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-3[/TD]
[TD="bgcolor: #FAFAFA"]STORE-3[/TD]
[TD="bgcolor: #FAFAFA"]STORE-3[/TD]
[TD="bgcolor: #FAFAFA"]STORE-3[/TD]
[TD="bgcolor: #FAFAFA"]STORE-3[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 9[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/4/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-5[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]OFFSITE[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 10[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/5/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 11[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/6/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 12[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/7/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 13[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/2/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]OFFSITE[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 14[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/3/2017[/TD]
[TD="bgcolor: #FAFAFA"]OFFSITE[/TD]
[TD="bgcolor: #FAFAFA"]OFFSITE[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 15[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/4/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]OFFSITE[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 16[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/5/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 17[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/6/2017[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 18[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/7/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 19[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/2/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 20[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/3/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 21[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/4/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 22[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/5/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 23[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/6/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 24[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/7/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 25[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/2/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"]1/2/2017[/TD]
[TD="align: right"]1/3/2017[/TD]
[TD="align: right"]1/4/2017[/TD]
[TD="align: right"]1/5/2017[/TD]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]1/7/2017[/TD]
[TD="align: right"]1/8/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B31[/TH]
[TD="align: left"]=SUMPRODUCT(($D$1:$J$1=B$29)*($B$2:$B$26=$A$30)*($D$2:$J$26=$A31))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the headings in B29:H30, put AM or PM in A30, and put the list of locations from A31 down. Then enter the formula in B31 and drag down and over. This pretty much ignores the date in column C.

Now if the date in C represents a training day where the employee will not be at the scheduled location on that day, then use this formula:

=SUMPRODUCT(($D$1:$J$1=B$29)*($B$2:$B$26=$A$30)*($D$2:$J$26=$A31)*($C$2:$C$26<>B$30))

 

[MZING81]

How about this?

	A	B	C	D	E	F	G	H	I	J
MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY
AM
STORE-1
STORE-2
STORE-3
STORE-4
STORE-5
KIOS-1
KIOS-2
KIOS-3
KIOS-4
WAREHOUSE
OFFSITE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]DATE[/TD]
[TD="bgcolor: #FAFAFA"]SUNDAY[/TD]
[TD="bgcolor: #FAFAFA"]MONDAY[/TD]
[TD="bgcolor: #FAFAFA"]TUESDAY[/TD]
[TD="bgcolor: #FAFAFA"]WEDNESDAY[/TD]
[TD="bgcolor: #FAFAFA"]THURSDAY[/TD]
[TD="bgcolor: #FAFAFA"]FRIDAY[/TD]
[TD="bgcolor: #FAFAFA"]SATURDAY[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 1[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/2/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 2[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/3/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 3[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/4/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 4[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/5/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 5[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/6/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 6[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/7/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 7[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/2/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 8[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/3/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-3[/TD]
[TD="bgcolor: #FAFAFA"]STORE-3[/TD]
[TD="bgcolor: #FAFAFA"]STORE-3[/TD]
[TD="bgcolor: #FAFAFA"]STORE-3[/TD]
[TD="bgcolor: #FAFAFA"]STORE-3[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 9[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/4/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-5[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]OFFSITE[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 10[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/5/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 11[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/6/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 12[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/7/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 13[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/2/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]OFFSITE[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 14[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/3/2017[/TD]
[TD="bgcolor: #FAFAFA"]OFFSITE[/TD]
[TD="bgcolor: #FAFAFA"]OFFSITE[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 15[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/4/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]OFFSITE[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 16[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/5/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 17[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/6/2017[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 18[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/7/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 19[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/2/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 20[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/3/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 21[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/4/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 22[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/5/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 23[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/6/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 24[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/7/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 25[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/2/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"]1/2/2017[/TD]
[TD="align: right"]1/3/2017[/TD]
[TD="align: right"]1/4/2017[/TD]
[TD="align: right"]1/5/2017[/TD]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]1/7/2017[/TD]
[TD="align: right"]1/8/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B31[/TH]
[TD="align: left"]=SUMPRODUCT(($D$1:$J$1=B$29)*($B$2:$B$26=$A$30)*($D$2:$J$26=$A31))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the headings in B29:H30, put AM or PM in A30, and put the list of locations from A31 down. Then enter the formula in B31 and drag down and over. This pretty much ignores the date in column C.

Now if the date in C represents a training day where the employee will not be at the scheduled location on that day, then use this formula:

=SUMPRODUCT(($D$1:$J$1=B$29)*($B$2:$B$26=$A$30)*($D$2:$J$26=$A31)*($C$2:$C$26<>B$30))

Thanks I appreciate the help and will give it's try!

 

[WarfritLive]

I think I understand now.

=SUMPRODUCT(--('Sheet 2'!E:E=$A3),--('Sheet 2'!$B:$B=$A$2),--('Sheet 2'!$C:$C<>B$2))
is the same, no?

 

[Eric W]

I'd have to test, but I believe that's essentially the same. The one thing you'd need to do is make sure that the date columns on both tables both go from Monday-Sunday, or Sunday-Saturday. As shown, they're not. I'd also recommend against full column references in a SUMPRODUCT for performance reasons.

 

[WarfritLive]

Right, just proof of concept. I'm on my phone so shorthand is preferable.

Not trying to second-guess you or anything. Just making sure I understand. Thanks!

 

[MZING81]

How about this?

	A	B	C	D	E	F	G	H	I	J
MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY
AM
STORE-1
STORE-2
STORE-3
STORE-4
STORE-5
KIOS-1
KIOS-2
KIOS-3
KIOS-4
WAREHOUSE
OFFSITE

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]DATE[/TD]
[TD="bgcolor: #FAFAFA"]SUNDAY[/TD]
[TD="bgcolor: #FAFAFA"]MONDAY[/TD]
[TD="bgcolor: #FAFAFA"]TUESDAY[/TD]
[TD="bgcolor: #FAFAFA"]WEDNESDAY[/TD]
[TD="bgcolor: #FAFAFA"]THURSDAY[/TD]
[TD="bgcolor: #FAFAFA"]FRIDAY[/TD]
[TD="bgcolor: #FAFAFA"]SATURDAY[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 1[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/2/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 2[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/3/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 3[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/4/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 4[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/5/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 5[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/6/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 6[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/7/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 7[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/2/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 8[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/3/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-3[/TD]
[TD="bgcolor: #FAFAFA"]STORE-3[/TD]
[TD="bgcolor: #FAFAFA"]STORE-3[/TD]
[TD="bgcolor: #FAFAFA"]STORE-3[/TD]
[TD="bgcolor: #FAFAFA"]STORE-3[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 9[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/4/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-5[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]OFFSITE[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 10[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/5/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 11[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/6/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 12[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/7/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 13[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/2/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]OFFSITE[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 14[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/3/2017[/TD]
[TD="bgcolor: #FAFAFA"]OFFSITE[/TD]
[TD="bgcolor: #FAFAFA"]OFFSITE[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 15[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/4/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]OFFSITE[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 16[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/5/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 17[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/6/2017[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA"]WAREHOUSE[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 18[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/7/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 19[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/2/2017[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 20[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/3/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-2[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 21[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/4/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 22[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/5/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 23[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/6/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA"]STORE-1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 24[/TD]
[TD="bgcolor: #FAFAFA"]PM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/7/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA"]KIOS-4[/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA"]STORE-0[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE 25[/TD]
[TD="bgcolor: #FAFAFA"]AM[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/2/2017[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]
[TD="bgcolor: #FAFAFA"]STORE-5[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"]1/2/2017[/TD]
[TD="align: right"]1/3/2017[/TD]
[TD="align: right"]1/4/2017[/TD]
[TD="align: right"]1/5/2017[/TD]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]1/7/2017[/TD]
[TD="align: right"]1/8/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B31[/TH]
[TD="align: left"]=SUMPRODUCT(($D$1:$J$1=B$29)*($B$2:$B$26=$A$30)*($D$2:$J$26=$A31))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the headings in B29:H30, put AM or PM in A30, and put the list of locations from A31 down. Then enter the formula in B31 and drag down and over. This pretty much ignores the date in column C.

Now if the date in C represents a training day where the employee will not be at the scheduled location on that day, then use this formula:

=SUMPRODUCT(($D$1:$J$1=B$29)*($B$2:$B$26=$A$30)*($D$2:$J$26=$A31)*($C$2:$C$26<>B$30))

Thanks this worked. Don't know why I didnt try a sumproduct sooner, guess I have been using countifs to replace them for so long I forgot that sumproducts are really useful.