Pls help on Sumproduct with >1 data range

[leecs]

Hi,
I had read many excellent samples & explanation from group. I try
to adopt some sample into my work but I fail to digest & use it. I
plan to get number of differences between 2 table (with multiple row &
column). A sample is my table:

(Table 1) (Table 2)
A B ...... F G
1 Anthony Coffee Anthony Coffee
2 Anthony Tea David Tea
3 David Tea Micheal Pepsi
4 Anthony Coffee Cloud
5 Anthony Pepsi
6 Micheal Coffee
7 Tea
8 Micheal Coffee
9 Cloud Tea
10 David Pepsi


I try to compare "column A VS partial column F" AND "column B VS
partial column G".

If I use
"=SUMPRODUCT(--((A1:A10=F1)+(A1:A10=F2)),--((B1:B10=G2)+(B1:B10=G3)))",
I able to get the answer which equal to 4.

But in actual, the criteria contain more than hundred of row, it is
not convenience for me to insert row by row. Then I try use the
formulas as
"=SUMPRODUCT(--(A1:A10=TRANSPOSE(F1:F2)),--(B1:B10=TRANSPOSE(G2:G3)))".
But the answer returned is only 2.

I knew that can be solved by VBA. But pls kindly teach me how to use
Excel function to solve it. Thank you very much.

 

[Bob Phillips]

Give this a try

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,F1:F3,0))),--(ISNUMBER(MATCH(B1:B10,G1:
G3,0))))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Roger Govier]

Hi Bob

Works absolutely fine, but criteria need to be F1:F2 and G2:G3 to match
OP's original request.
Otherwise he will return an answer of 8, not 4.

 

[Bob Phillips]

Yeah I saw that Roger, but I assumed (I know you never should ) that that
was a typo. I just couldn't see any rationale on offsetting it.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

And I also extended it by a line to demonstrate how it would work.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[leecs]

Hi All,
It work fine. Thanks a lot.