Using Vlookup with Sumproduct to satisfy multiple conditions

S

shawnteojc

Hi everyone,

I have been pondering this for many days without being able to obtain
a solution. With the following sample data,

Table 1
City Country
Sydney Australia
Madrid Spain
Lisbon Portugal
Brisbane Australia
Tokyo Japan
Geneva Switzerland
Zurich Switzerland



Table 2
# of Visits Favorite Birthplace
2 Sydney Switzerland
1 Zurich Switzerland
4 Tokyo Australia
2 Brisbane Spain
3 Tokyo Japan
2 Lisbon Japan
3 Madrid Switzerland
1 Madrid Japan
3 Zurich Australia


I would like to calculate the number of entries where the number of
visits is either 2 or 3 AND that the Favorite City is not located in
the same Birthplace, ie. (3 Tokyo Japan) should not be considered but
(3 Madrid Switzerland) should?

Is it possible to use a combination of vlookup and sumproduct to
obtain the answer? If possible I would prefer not to add new columns
to Table 2.

Really appreciate any help. Thanks in advance.


Shawn
 
M

Max

Until something better drifts by ..

Data for table 1 asssumed in A3:B9,
that for table 2 assumed in A14:C22

In D14, copied down to D22:
=--(INDEX($B$3:$B$9,MATCH(B14,$A$3:$A$9,0))=C14)

D14:D22 provides the references to handle that criteria bit on the city <>
birthplace

Then you could use in say, A25:
=SUMPRODUCT((A14:A22={2,3})*(D14:D22=0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik
 
S

shawnteojc

Max,

Thank you.

the formula works. A few questions though:

1) is it possible to do the job without adding a new column to table
2?

2) Could you enlighten me on the use of "--" preceding your formula?

Thanks.


Cheers,
Shawn
 
M

Max

Shawn
the formula works.
Click to expand...
That's good, because that's usually the most important thing at hand
1) is it possible to do the job without adding a new column to table2?
Click to expand...
I couldn't find a way to do this. That's why my topliner:But maybe other responders do. Hang around awhile.
2) Could you enlighten me on the use of "--" preceding your formula?
Click to expand...
The "--" will coerce TRUEs/FALSEs returned by the comparison formula:
INDEX($B$3:$B$9,MATCH(B14,$A$3:$A$9,0))=C14
to numeric 1's/0's
(Sumproduct works with numbers)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,700, Files: 356, Subscribers: 53
xdemechanik
---
 
S

shawnteojc

I have tried using the LOOKUP function for this as well but I think
there is a problem, meaning the LOOKUP function doesn't seem to return
the correct values. I may not have understood this correctly.

This is the formula i am using:

Data for table 1 asssumed in A3:B9,
that for table 2 assumed in A14:C22

=SUMPRODUCT((A14:A22={2,3})*(LOOKUP(B14:B22,A3:B9)<>C14:C22))

Perhaps i am not using the LOOKUP correctly.

Would really appreciate any help.

Thanks.


Shawn
 
S

shawnteojc

I have triedusingthe LOOKUP function for this as well but I think
there is a problem, meaning the LOOKUP function doesn't seem to return
the correct values. I may not have understood this correctly.
Click to expand...
Hi I am still stuck on this.

Is there no one who could help me with this?

Thanks in advance.


This is the formula i amusing:
 
T

T. Valko

I *think* this does what you want. Based on your posted sample data I get a
result of 5.

Table 1 in the range A2:B8
Table 2 in the range D2:F10

=SUMPRODUCT((D2:D10={2,3})*(ISNA(MATCH(E2:E10&F2:F10,A2:A8&B2:B8,0))))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using VLOOKUP to Sum values from multiple columns 1
Array formula using sumproduct & vlookup 6
Using Sumproduct Function To Add Multiple Criteria 1
linking data from a web to access 6
Sumproduct with a vLookup, multiple criteria 3
Breaking my head - TimeZone Conversion 3
Using VLOOKUP with multiple first column matches 2
Using VLOOKUP with abitlity to choose from multiple defined names. 5

Top