COUNTIF+INDEX+MATCH formula

M

MZING81

Board Regular
Joined
Mar 20, 2012
Messages
74
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.
DATESUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
EMPLOYEE 1AM1/2/2017KIOS-4KIOS-4STORE-5STORE-5KIOS-4
EMPLOYEE 2AM1/3/2017 KIOS-1KIOS-1STORE-1STORE-1
EMPLOYEE 3AM1/4/2017 STORE-1STORE-1STORE-1STORE-1STORE-1
EMPLOYEE 4AM1/5/2017STORE-1STORE-1STORE-1 STORE-1STORE-1
EMPLOYEE 5PM1/6/2017 STORE-0 STORE-0STORE-0STORE-0
EMPLOYEE 6PM1/7/2017KIOS-1STORE-1 STORE-1KIOS-1KIOS-1
EMPLOYEE 7PM1/2/2017 STORE-2 STORE-2 STORE-2
EMPLOYEE 8AM1/3/2017 STORE-3STORE-3STORE-3STORE-3STORE-3
EMPLOYEE 9AM1/4/2017 STORE-5KIOS-5KIOS-4STORE-5OFFSITE
EMPLOYEE 10AM1/5/2017STORE-5STORE-5 STORE-5STORE-5STORE-5
EMPLOYEE 11PM1/6/2017 STORE-1STORE-1STORE-1STORE-1STORE-1
EMPLOYEE 12PM1/7/2017STORE-2STORE-2 STORE-2STORE-2STORE-2
EMPLOYEE 13PM1/2/2017KIOS-1STORE-2KIOS-1KIOS-1OFFSITE
EMPLOYEE 14AM1/3/2017OFFSITEOFFSITESTORE-2 KIOS-1
EMPLOYEE 15AM1/4/2017KIOS-1OFFSITE STORE-2KIOS-1KIOS-1
EMPLOYEE 16AM1/5/2017 WAREHOUSEWAREHOUSEWAREHOUSESTORE-1
EMPLOYEE 17AM1/6/2017WAREHOUSEWAREHOUSESTORE-5WAREHOUSEWAREHOUSE
EMPLOYEE 18PM1/7/2017KIOS-1KIOS-1KIOS-1KIOS-1 STORE-1
EMPLOYEE 19PM1/2/2017KIOS-1KIOS-1 KIOS-1KIOS-1
EMPLOYEE 20PM1/3/2017 KIOS-1STORE-2KIOS-1STORE-2KIOS-1
EMPLOYEE 21AM1/4/2017STORE-1STORE-1STORE-1STORE-1STORE-1
EMPLOYEE 22AM1/5/2017STORE-1STORE-1STORE-1STORE-1STORE-1
EMPLOYEE 23PM1/6/2017 STORE-1STORE-1STORE-1STORE-1STORE-1
EMPLOYEE 24PM1/7/2017 STORE-0KIOS-4KIOS-4STORE-0STORE-0
EMPLOYEE 25AM1/2/2017STORE-5STORE-5 STORE-5STORE-5STORE-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
MONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAY
AM1/2/20171/3/20171/4/20171/5/20171/6/20171/7/20171/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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
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?
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
How about this?

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><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))

<thead>
</thead><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))
 
Upvote 0
Eric W said:
How about this?

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><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))

<thead>
</thead><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))
Click to expand...

Thanks I appreciate the help and will give it's try!
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
Eric W said:
How about this?

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))
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
369
Alex Blakenburg
A
M
  • Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
337
mrshuck
M
k3yn0t3
  • Solved
XLOOKUP (multiple criteria) not working
Replies
3
Views
302
k3yn0t3
k3yn0t3
J
  • Question
Sumproduct with different array sizes
2
Replies
19
Views
441
jkj115
J
V
  • Question
How do I insert images in next row?
Replies
5
Views
535
jolivanes
jolivanes

Forum statistics

Threads
1,214,528
Messages
6,120,065
Members
448,941
Latest member
AlphaRino

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top