2-D / Matrix usage of COUNTIFS or SUMPRODUCT(ISNUMBER(SEARCH()))

[dmessmer]

Hello,

1st time poster here. I am trying to create a countif or a sumproduct which: 1) recognizes which column it needs to count from, and then counts based upon if the entry in that column meets another criteria. So in this example, cell B3 would look at all of Row 1, find 'Tom' in Column E, and then return a Count from that Column where E:E = D:D. So since E2 = D2, E3 = D3, and E4 = D4, it returns a count of 3. But for B4 ('Jack'), it only returns a count of 1 because Column G only contains 1 worthy instance (where G2 = D2).

I should also note that in the example everything is on the same worksheet. But in practicality Columns A & B are on Worksheet 1 and Columns D-H are on Worksheet 2. However I assume the underlying logic would still be the same? If possible, I would like to avoid using array formulas.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Person[/TD]
[TD]Count of Matching Fruit[/TD]
[TD][/TD]
[TD]Fruit to Match[/TD]
[TD]Tom[/TD]
[TD]Bob[/TD]
[TD]Jack[/TD]
[TD]Jill[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Tom[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]Apple[/TD]
[TD]Grape[/TD]
[TD]Apple[/TD]
[TD]Grape[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bob[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD]Orange[/TD]
[TD]Orange[/TD]
[TD]Kiwi[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jack[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Pear[/TD]
[TD]Pear[/TD]
[TD]Pear[/TD]
[TD]Watermelon[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jill[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the forum.

Try:

	A	B	C	D	E	F	G	H
Person	Count of Matching Fruit	Fruit to Match	Tom	Bob	Jack	Jill
Tom	Apple	Apple	Grape	Apple	Grape
Bob	Orange	Orange	Orange	Kiwi	Orange
Jack	Pear	Pear	Pear	Watermelon	Pear
Jill

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=SUMPRODUCT(--($D$2:$D$10=OFFSET($D$2:$D$10,0,MATCH(A2,$E$1:$K$1,0))),--($D$2:$D$10<>""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Since your tables will be on different sheets, you'll need to adjust the ranges, and the MATCH may require an offset.

 

[steve the fish]

Heres a way:

=SUMPRODUCT(--(INDEX($E$2:$H$4,,MATCH(A2,$E$1:$H$1,0))=$D$2:$D$4))