Simplify Sumproduct, Multiple Entries

[the.yangist]

I have a bit of a problem. I'm using a spreadsheet on WordNet (a large SQL database turned to cells), but have a need for a special kind of function that I simply don't know.

I have been using SUMPRODUCT dominantly to sort the data as I've needed it, but now I'm in need of a single-method lookup.

Here's the gist:

A1:A100
B1:B4

In C1:C10, SUMPRODUCT(($A$1:$A$100=$B1)+($A$1:$A$100=$B2)+($A$1:$A$100=$B3)+($A$1:$A$100=$B4)...)

The issue is that I have 5000 B cells to look up.

There is a longer solution to my problem, but I was curious if an array formula was out there that could solve this matter more shortly.

 

[MrKowz]

What exactly are you trying to do? I'm confused

 

[T. Valko]

I have a bit of a problem. I'm using a spreadsheet on WordNet (a large SQL database turned to cells), but have a need for a special kind of function that I simply don't know.

I have been using SUMPRODUCT dominantly to sort the data as I've needed it, but now I'm in need of a single-method lookup.

Here's the gist:

A1:A100
B1:B4

In C1:C10, SUMPRODUCT(($A$1:$A$100=$B1)+($A$1:$A$100=$B2)+($A$1:$A$100=$B3)+($A$1:$A$100=$B4)...)

The issue is that I have 5000 B cells to look up.

There is a longer solution to my problem, but I was curious if an array formula was out there that could solve this matter more shortly.

Like this...

=SUMPRODUCT(--(ISNUMBER(MATCH($A$1:$A$100,$B$1:$B$4,0))))

 

[the.yangist]

I'm checking a pretty involved dictionary database for matches to a frequency corpus.

The question is basically, "How many corpus entries match the following dictionary entries?" I can concatenate the criteria as I need to check them, but the SUMPRODUCT formula, itself, would be too long to check for 5000+ corpus values.

 

[Jonmo1]

Try this array entered with CTRL + SHIFT + ENTER

=SUM(COUNTIF(A1:A100,B1:B10))


edit..

Or without CTRL + SHIFT + ENTER

=SUMPRODUCT(COUNTIF(A1:A100,B1:B10))

 

[the.yangist]

Like this...

=SUMPRODUCT(--(ISNUMBER(MATCH($A$1:$A$100,$B$1:$B$4,0))))

Does the MATCH function look for injections or surjections for whole arrays? How does that part work?

 

[T. Valko]

Does the MATCH function look for injections or surjections for whole arrays? How does that part work?

Book1
	A	B
1	1	3
2	2	5
3	3	9
4	4	11
5	5	_
6	6	_
7	7	_
8	8	_
9	9	_
10	10	_
Sheet1

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,B1:B4,0))))

=3

The 3 in column A matches the 3 in column B
The 5 in column A matches the 5 in column B
The 9 in column A matches the 9 in column B

 

[the.yangist]

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,B1:B4,0))))

=3

The 3 in column A matches the 3 in column B
The 5 in column A matches the 5 in column B
The 9 in column A matches the 9 in column B

That's clearer. Much obliged!

 

[T. Valko]

That's clearer. Much obliged!

You're welcome!