COUNTIFS Using Multiple Index / Match Lookups..

[BenGee]

Hi,

I've currently got;

=COUNTIFS(INDEX('Training Levels'!B5:K32,MATCH(Shifts!A$5,'Training Levels'!A5:A32,0),MATCH(Shifts!A4,'Training Levels'!B4:K4,0)),1,Shifts!B5,"ASH")

But I'm looking to do this for everyone row that meets this criteria between B5:B35. Something I thought but doesn't work is;
=COUNTIFS(INDEX('Training Levels'!B5:K32,MATCH(Shifts!A5:A35,'Training Levels'!A5:A32,0),MATCH(Shifts!A4,'Training Levels'!B4:K4,0)),1,Shifts!B5:35,"ASH")

Shifts!A5:A35 has a list of names
Shifts!A4 has a product name
Shifts!B5:B35 contain either E, L, MS, ASH, HDAM, HDPM (which represents the shift this person is on)
'Training Levels'!B5:K32 contain either 0 or 1 (0 = not training, 1 = trained)
'Training Levels'!A5:A32 is the same list of names
'Training Levels'!B4:K4 is the list of products

Hope this makes sense.

Thanks in advance

 

[Eric W]

It would help if you could post an extract with the input data and the expected results. It sounds like you want a formula to put in Shifts!C5 and drag it down, allowing you to see who on a given shift is trained on a given product. If so, consider this:

	A	B	C
Pants	ASH
Al	E
Betty	E
Cal	L
Daphne	MS
Ed	ASH
Farrah	HDAM
Greg	HDPM
Hillary	ASH

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Shifts​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C5[/TH]
[TD="align: left"]=INDEX('Training Levels'!$B$5:$K$32,MATCH(A5,'Training Levels'!$A$5:$A$32,0),MATCH($A$4,'Training Levels'!$B$4:$K$4,0))*($B$4=B5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



This checks the name (A5) against the list on Training Sheets, and the product ($A$4) against the list on Training Sheets, and returns the 1 or 0 at the intersection. It then checks the person's shift (B5) against the desired shift ($B$4). If I understand your question properly, your original formula might work, if you put the $ sign (the absolute reference indicator) in the right places.

Hope this helps.

 

[BenGee]

Hi Eric. Thank you for your help so far

This is exactly what I'm looking for;
(SUM(COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$5,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B5,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$6,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B6,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$7,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B7,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$9,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B9,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$10,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B10,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$11,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B11,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$12,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B12,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$13,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B13,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$14,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B14,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$15,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B15,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$16,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B16,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$17,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B17,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$19,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B19,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$20,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B20,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$21,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B21,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$22,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B22,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$23,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B23,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$24,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B24,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$26,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B26,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$27,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B27,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$28,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B28,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$29,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B29,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$30,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B30,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$31,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B31,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$32,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B32,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$33,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B33,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$34,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B34,{"ASH","HDAM"}),COUNTIFS(INDEX('Training Levels'!$B$5:$K$32,MATCH(Shifts!$A$35,'Training Levels'!$A$5:$A$32,0),MATCH(Shifts!$A$4,'Training Levels'!$B$4:$K$4,0)),1,B35,{"ASH","HDAM"})))

But I'm wondering if there is a more simple way of writing this as I'm going to have re-write / adapt >20 times.

Hope this helps

 

[Eric W]

Short answer is yes, that can be rewritten shorter. It has a lot of repetition in it. However, I'm not going to parse the whole thing. I looked at the top few lines and came up with:

=SUMPRODUCT('Training Levels'!$B$5:$K$32*('Training Levels'!$B$4:$K$4=Shifts!A4)*(ISNUMBER(MATCH('Training Levels'!$A$5:$A$32&"|[COLOR=#ff0000]ASH[/COLOR]",Shifts!$A$5:$A$35&"|"&Shifts!$B$5:$B$35,0))))+
SUMPRODUCT('Training Levels'!$B$5:$K$32*('Training Levels'!$B$4:$K$4=Shifts!A4)*(ISNUMBER(MATCH('Training Levels'!$A$5:$A$32&"|[COLOR=#ff0000]HDAM[/COLOR]",Shifts!$A$5:$A$35&"|"&Shifts!$B$5:$B$35,0))))

The 2 SUMPRODUCTS are the same except for the shift in red. I could probably combine them at the risk of making it more unintelligible.

If this doesn't work for you, it would be VERY helpful if you could show a sample of your two sheets, and the expected results. It would be MUCH easier to figure out a solution for you from actual data (not just a description), and a description in words of what you want, and the actual results for a given set of data.

 

[BenGee]

Hi Eric - That is perfect, thanks for the help. Helped a lot