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]

Hi,

2 ways, 1st is an array formula, 2nd is regular:

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	=SUM(COUNTIF(A1:A5,D2:H2))
C3	C3	=SUMPRODUCT(COUNTIF(A1:A5,D2:H2))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[matt_ward]

The SUMPRODUCT!!!! Dang! Thank you very much! Liked the first one but im going to need to do this over a large table so Sumproduct it is! Thanks again!

 

[matt_ward]

Hi,

2 ways, 1st is an array formula, 2nd is regular:

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	=SUM(COUNTIF(A1:A5,D2:H2))
C3	C3	=SUMPRODUCT(COUNTIF(A1:A5,D2:H2))
Press CTRL+SHIFT+ENTER to enter array formulas.

Do you think there is a simpler way of doing this that is less punishing for excel? I have 85,000 rows and a list of 18,000 values... Thinking this probebly DB time.

 

[matt_ward]

for example...

Apple			Apple	dog	lemon	cat	peach	Santa	pear
pear			Apple	cat
lemon			Santa	peach	pear
peach			Pear	dog	peach	pear

The formular would be instead of having a formula that i copy down the range... if it adapted to the number of items that needed calculating that would speed things up... So some how get the results of a COUNTA(D1:J1) where the answer (7) becomes part of the CountIF array. Would ADDRESS do that?

 

[jtakw]

Hi,

I'm glad the formulas provided did answer your OP.
I can't think of a way that's "less resource intensive" for excel since your data involves 2 arrays...
And I'm not understanding your latest sample in post #5, are you thinking of restructuring your data set to make calculations more efficient?
Please elaborate, thank you.

 

[matt_ward]

So in my actual spreadsheet I have 23 Columns over 80,000 rows. I could copy and paste the fomular down but it spends time evaluating empty Cells. All the cells that need evaluating are Aligned to the left (for want of a better term)... I've figured out i can use CountA to work out how many Cells contain data (not Blank) Im just working a way to have that built into the formula so that every calculation only calculates the cells that have data in them. I tried creating a Concatenated string and see if could use that but that doesn't work for some reason. Been looking at the Functions OFFSET and ROW and ADDRESS but can't seam to cobble it together.

 

[matt_ward]

So in your answer... the H2 value is dynamic and based on the counta result... so if CountA evaluated 5 columns it would be H2 if it had evaluated it as 4 columns it would be E2.

=SUMPRODUCT(COUNTIF(A1:A5,D2:H2))

 

[matt_ward]

Not E2 sorry it would be G2

 

[matt_ward]

Sort of accepted i have to subsitute both parts of the reference D2:H2 with an equation which is equilviant based ont he evaluation.