# COUNTIF+INDEX+MATCH formula

#### MZING81

##### Board Regular
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

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Eric W

##### MrExcel MVP
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

##### Board Regular
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

##### Board Regular
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.

Last edited:

#### Eric W

##### MrExcel MVP

ABCDEFGHIJ
1DATESUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
2EMPLOYEE 1AM1/2/2017KIOS-4KIOS-4STORE-5STORE-5KIOS-4
3EMPLOYEE 2AM1/3/2017KIOS-1KIOS-1STORE-1STORE-1
4EMPLOYEE 3AM1/4/2017STORE-1STORE-1STORE-1STORE-1STORE-1
5EMPLOYEE 4AM1/5/2017STORE-1STORE-1STORE-1STORE-1STORE-1
6EMPLOYEE 5PM1/6/2017STORE-0STORE-0STORE-0STORE-0
7EMPLOYEE 6PM1/7/2017KIOS-1STORE-1STORE-1KIOS-1KIOS-1
8EMPLOYEE 7PM1/2/2017STORE-2STORE-2STORE-2
9EMPLOYEE 8AM1/3/2017STORE-3STORE-3STORE-3STORE-3STORE-3
10EMPLOYEE 9AM1/4/2017STORE-5KIOS-5KIOS-4STORE-5OFFSITE
11EMPLOYEE 10AM1/5/2017STORE-5STORE-5STORE-5STORE-5STORE-5
12EMPLOYEE 11PM1/6/2017STORE-1STORE-1STORE-1STORE-1STORE-1
13EMPLOYEE 12PM1/7/2017STORE-2STORE-2STORE-2STORE-2STORE-2
14EMPLOYEE 13PM1/2/2017KIOS-1STORE-2KIOS-1KIOS-1OFFSITE
15EMPLOYEE 14AM1/3/2017OFFSITEOFFSITESTORE-2KIOS-1
16EMPLOYEE 15AM1/4/2017KIOS-1OFFSITESTORE-2KIOS-1KIOS-1
17EMPLOYEE 16AM1/5/2017WAREHOUSEWAREHOUSEWAREHOUSESTORE-1
18EMPLOYEE 17AM1/6/2017WAREHOUSEWAREHOUSESTORE-5WAREHOUSEWAREHOUSE
19EMPLOYEE 18PM1/7/2017KIOS-1KIOS-1KIOS-1KIOS-1STORE-1
20EMPLOYEE 19PM1/2/2017KIOS-1KIOS-1KIOS-1KIOS-1
21EMPLOYEE 20PM1/3/2017KIOS-1STORE-2KIOS-1STORE-2KIOS-1
22EMPLOYEE 21AM1/4/2017STORE-1STORE-1STORE-1STORE-1STORE-1
23EMPLOYEE 22AM1/5/2017STORE-1STORE-1STORE-1STORE-1STORE-1
24EMPLOYEE 23PM1/6/2017STORE-1STORE-1STORE-1STORE-1STORE-1
25EMPLOYEE 24PM1/7/2017STORE-0KIOS-4KIOS-4STORE-0STORE-0
26EMPLOYEE 25AM1/2/2017STORE-5STORE-5STORE-5STORE-5STORE-5
27
28
29MONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAY
30AM1/2/20171/3/20171/4/20171/5/20171/6/20171/7/20171/8/2017
31STORE-14445213
32STORE-20101000
33STORE-31111100
34STORE-40000000
35STORE-53213222
36KIOS-11100121
37KIOS-20000000
38KIOS-30000000
39KIOS-41011001
40WAREHOUSE2121001
41OFFSITE2000101

</tbody>
Sheet1

Worksheet Formulas
CellFormula
B31=SUMPRODUCT((\$D\$1:\$J\$1=B\$29)*(\$B\$2:\$B\$26=\$A\$30)*(\$D\$2:\$J\$26=\$A31))

</tbody>

<tbody>
</tbody>

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

##### Board Regular

ABCDEFGHIJ
1DATESUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
2EMPLOYEE 1AM1/2/2017KIOS-4KIOS-4STORE-5STORE-5KIOS-4
3EMPLOYEE 2AM1/3/2017KIOS-1KIOS-1STORE-1STORE-1
4EMPLOYEE 3AM1/4/2017STORE-1STORE-1STORE-1STORE-1STORE-1
5EMPLOYEE 4AM1/5/2017STORE-1STORE-1STORE-1STORE-1STORE-1
6EMPLOYEE 5PM1/6/2017STORE-0STORE-0STORE-0STORE-0
7EMPLOYEE 6PM1/7/2017KIOS-1STORE-1STORE-1KIOS-1KIOS-1
8EMPLOYEE 7PM1/2/2017STORE-2STORE-2STORE-2
9EMPLOYEE 8AM1/3/2017STORE-3STORE-3STORE-3STORE-3STORE-3
10EMPLOYEE 9AM1/4/2017STORE-5KIOS-5KIOS-4STORE-5OFFSITE
11EMPLOYEE 10AM1/5/2017STORE-5STORE-5STORE-5STORE-5STORE-5
12EMPLOYEE 11PM1/6/2017STORE-1STORE-1STORE-1STORE-1STORE-1
13EMPLOYEE 12PM1/7/2017STORE-2STORE-2STORE-2STORE-2STORE-2
14EMPLOYEE 13PM1/2/2017KIOS-1STORE-2KIOS-1KIOS-1OFFSITE
15EMPLOYEE 14AM1/3/2017OFFSITEOFFSITESTORE-2KIOS-1
16EMPLOYEE 15AM1/4/2017KIOS-1OFFSITESTORE-2KIOS-1KIOS-1
17EMPLOYEE 16AM1/5/2017WAREHOUSEWAREHOUSEWAREHOUSESTORE-1
18EMPLOYEE 17AM1/6/2017WAREHOUSEWAREHOUSESTORE-5WAREHOUSEWAREHOUSE
19EMPLOYEE 18PM1/7/2017KIOS-1KIOS-1KIOS-1KIOS-1STORE-1
20EMPLOYEE 19PM1/2/2017KIOS-1KIOS-1KIOS-1KIOS-1
21EMPLOYEE 20PM1/3/2017KIOS-1STORE-2KIOS-1STORE-2KIOS-1
22EMPLOYEE 21AM1/4/2017STORE-1STORE-1STORE-1STORE-1STORE-1
23EMPLOYEE 22AM1/5/2017STORE-1STORE-1STORE-1STORE-1STORE-1
24EMPLOYEE 23PM1/6/2017STORE-1STORE-1STORE-1STORE-1STORE-1
25EMPLOYEE 24PM1/7/2017STORE-0KIOS-4KIOS-4STORE-0STORE-0
26EMPLOYEE 25AM1/2/2017STORE-5STORE-5STORE-5STORE-5STORE-5
27
28
29MONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAY
30AM1/2/20171/3/20171/4/20171/5/20171/6/20171/7/20171/8/2017
31STORE-14445213
32STORE-20101000
33STORE-31111100
34STORE-40000000
35STORE-53213222
36KIOS-11100121
37KIOS-20000000
38KIOS-30000000
39KIOS-41011001
40WAREHOUSE2121001
41OFFSITE2000101

</tbody>
Sheet1

Worksheet Formulas
CellFormula
B31=SUMPRODUCT((\$D\$1:\$J\$1=B\$29)*(\$B\$2:\$B\$26=\$A\$30)*(\$D\$2:\$J\$26=\$A31))

</tbody>

<tbody>
</tbody>

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

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular
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

##### Board Regular

ABCDEFGHIJ
1DATESUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
2EMPLOYEE 1AM1/2/2017KIOS-4KIOS-4STORE-5STORE-5KIOS-4
3EMPLOYEE 2AM1/3/2017KIOS-1KIOS-1STORE-1STORE-1
4EMPLOYEE 3AM1/4/2017STORE-1STORE-1STORE-1STORE-1STORE-1
5EMPLOYEE 4AM1/5/2017STORE-1STORE-1STORE-1STORE-1STORE-1
6EMPLOYEE 5PM1/6/2017STORE-0STORE-0STORE-0STORE-0
7EMPLOYEE 6PM1/7/2017KIOS-1STORE-1STORE-1KIOS-1KIOS-1
8EMPLOYEE 7PM1/2/2017STORE-2STORE-2STORE-2
9EMPLOYEE 8AM1/3/2017STORE-3STORE-3STORE-3STORE-3STORE-3
10EMPLOYEE 9AM1/4/2017STORE-5KIOS-5KIOS-4STORE-5OFFSITE
11EMPLOYEE 10AM1/5/2017STORE-5STORE-5STORE-5STORE-5STORE-5
12EMPLOYEE 11PM1/6/2017STORE-1STORE-1STORE-1STORE-1STORE-1
13EMPLOYEE 12PM1/7/2017STORE-2STORE-2STORE-2STORE-2STORE-2
14EMPLOYEE 13PM1/2/2017KIOS-1STORE-2KIOS-1KIOS-1OFFSITE
15EMPLOYEE 14AM1/3/2017OFFSITEOFFSITESTORE-2KIOS-1
16EMPLOYEE 15AM1/4/2017KIOS-1OFFSITESTORE-2KIOS-1KIOS-1
17EMPLOYEE 16AM1/5/2017WAREHOUSEWAREHOUSEWAREHOUSESTORE-1
18EMPLOYEE 17AM1/6/2017WAREHOUSEWAREHOUSESTORE-5WAREHOUSEWAREHOUSE
19EMPLOYEE 18PM1/7/2017KIOS-1KIOS-1KIOS-1KIOS-1STORE-1
20EMPLOYEE 19PM1/2/2017KIOS-1KIOS-1KIOS-1KIOS-1
21EMPLOYEE 20PM1/3/2017KIOS-1STORE-2KIOS-1STORE-2KIOS-1
22EMPLOYEE 21AM1/4/2017STORE-1STORE-1STORE-1STORE-1STORE-1
23EMPLOYEE 22AM1/5/2017STORE-1STORE-1STORE-1STORE-1STORE-1
24EMPLOYEE 23PM1/6/2017STORE-1STORE-1STORE-1STORE-1STORE-1
25EMPLOYEE 24PM1/7/2017STORE-0KIOS-4KIOS-4STORE-0STORE-0
26EMPLOYEE 25AM1/2/2017STORE-5STORE-5STORE-5STORE-5STORE-5
27
28
29MONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAY
30AM1/2/20171/3/20171/4/20171/5/20171/6/20171/7/20171/8/2017
31STORE-14445213
32STORE-20101000
33STORE-31111100
34STORE-40000000
35STORE-53213222
36KIOS-11100121
37KIOS-20000000
38KIOS-30000000
39KIOS-41011001
40WAREHOUSE2121001
41OFFSITE2000101

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
B31=SUMPRODUCT((\$D\$1:\$J\$1=B\$29)*(\$B\$2:\$B\$26=\$A\$30)*(\$D\$2:\$J\$26=\$A31))

<tbody>
</tbody>

<tbody>
</tbody>

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.

Replies
4
Views
43
Replies
2
Views
84
Replies
11
Views
525
Replies
0
Views
262
Replies
2
Views
276

1,190,897
Messages
5,983,444
Members
439,843
Latest member
PlanetFitness

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back