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.

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

<tbody>
</tbody>

 

[Eric W]

Welcome to the forum.

Try:

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

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B2 =SUMPRODUCT(--($D$2:$D$10=OFFSET($D$2:$D$10,0,MATCH(A2,$E$1:$K$1,0))),--($D$2:$D$10<>"")) <tbody> </tbody>

<tbody>
</tbody>

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))