Sort of Count if but using and array across ROWs.

[matt_ward]

So... bit stumped on this but here goes.

Say I have a list of unique values that are fruit in a vertical list

Apple		result
Pear		3	apple	pear	tree	Dog	Lemon
Orange
Grapes
Lemon

I want to be able to count how many times things in the vertical list are seen in the Horizontal list. so in the above the answer is 3 as Apple, pear & lemon are in the list.

this possible?? thinking it involves some sort of Index and count?

 

[jtakw]

See if this formula in C3 helps a little:

Book3.xlsx
	A	B	C	D	E	F	G	H
1	Apple		result
2	Pear		3	apple	pear	tree	Dog	Lemon
3	Orange		3
4	Grapes
5	Lemon
Sheet935

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(COUNTIF(A1:A5,D2:H2))
C3	C3	=SUMPRODUCT(--(A1:A5=D2:H2))