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.

		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

<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

	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

<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

 

[Eric W]

Glad we could help! SUMPRODUCT does have a few tricks that COUNTIFS can't do.