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.
<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>
</tbody>
This is where I would like to fill the data
<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>
</tbody>
Thanks
Mzing81
=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.
DATE | SUNDAY | MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY | SATURDAY | ||
EMPLOYEE 1 | AM | 1/2/2017 | KIOS-4 | KIOS-4 | STORE-5 | STORE-5 | KIOS-4 | ||
EMPLOYEE 2 | AM | 1/3/2017 | KIOS-1 | KIOS-1 | STORE-1 | STORE-1 | |||
EMPLOYEE 3 | AM | 1/4/2017 | STORE-1 | STORE-1 | STORE-1 | STORE-1 | STORE-1 | ||
EMPLOYEE 4 | AM | 1/5/2017 | STORE-1 | STORE-1 | STORE-1 | STORE-1 | STORE-1 | ||
EMPLOYEE 5 | PM | 1/6/2017 | STORE-0 | STORE-0 | STORE-0 | STORE-0 | |||
EMPLOYEE 6 | PM | 1/7/2017 | KIOS-1 | STORE-1 | STORE-1 | KIOS-1 | KIOS-1 | ||
EMPLOYEE 7 | PM | 1/2/2017 | STORE-2 | STORE-2 | STORE-2 | ||||
EMPLOYEE 8 | AM | 1/3/2017 | STORE-3 | STORE-3 | STORE-3 | STORE-3 | STORE-3 | ||
EMPLOYEE 9 | AM | 1/4/2017 | STORE-5 | KIOS-5 | KIOS-4 | STORE-5 | OFFSITE | ||
EMPLOYEE 10 | AM | 1/5/2017 | STORE-5 | STORE-5 | STORE-5 | STORE-5 | STORE-5 | ||
EMPLOYEE 11 | PM | 1/6/2017 | STORE-1 | STORE-1 | STORE-1 | STORE-1 | STORE-1 | ||
EMPLOYEE 12 | PM | 1/7/2017 | STORE-2 | STORE-2 | STORE-2 | STORE-2 | STORE-2 | ||
EMPLOYEE 13 | PM | 1/2/2017 | KIOS-1 | STORE-2 | KIOS-1 | KIOS-1 | OFFSITE | ||
EMPLOYEE 14 | AM | 1/3/2017 | OFFSITE | OFFSITE | STORE-2 | KIOS-1 | |||
EMPLOYEE 15 | AM | 1/4/2017 | KIOS-1 | OFFSITE | STORE-2 | KIOS-1 | KIOS-1 | ||
EMPLOYEE 16 | AM | 1/5/2017 | WAREHOUSE | WAREHOUSE | WAREHOUSE | STORE-1 | |||
EMPLOYEE 17 | AM | 1/6/2017 | WAREHOUSE | WAREHOUSE | STORE-5 | WAREHOUSE | WAREHOUSE | ||
EMPLOYEE 18 | PM | 1/7/2017 | KIOS-1 | KIOS-1 | KIOS-1 | KIOS-1 | STORE-1 | ||
EMPLOYEE 19 | PM | 1/2/2017 | KIOS-1 | KIOS-1 | KIOS-1 | KIOS-1 | |||
EMPLOYEE 20 | PM | 1/3/2017 | KIOS-1 | STORE-2 | KIOS-1 | STORE-2 | KIOS-1 | ||
EMPLOYEE 21 | AM | 1/4/2017 | STORE-1 | STORE-1 | STORE-1 | STORE-1 | STORE-1 | ||
EMPLOYEE 22 | AM | 1/5/2017 | STORE-1 | STORE-1 | STORE-1 | STORE-1 | STORE-1 | ||
EMPLOYEE 23 | PM | 1/6/2017 | STORE-1 | STORE-1 | STORE-1 | STORE-1 | STORE-1 | ||
EMPLOYEE 24 | PM | 1/7/2017 | STORE-0 | KIOS-4 | KIOS-4 | STORE-0 | STORE-0 | ||
EMPLOYEE 25 | AM | 1/2/2017 | STORE-5 | STORE-5 | STORE-5 | STORE-5 | STORE-5 |
This is where I would like to fill the data
MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY | SATURDAY | SUNDAY | |
AM | 1/2/2017 | 1/3/2017 | 1/4/2017 | 1/5/2017 | 1/6/2017 | 1/7/2017 | 1/8/2017 |
STORE-1 | ?? | ||||||
STORE-2 | |||||||
STORE-3 | |||||||
WAREHOUSE | |||||||
KIOS-1 |
Thanks
Mzing81