Using Vlookup with Sumproduct to satisfy multiple conditions

  • Thread starter Thread starter shawnteojc
  • Start date Start date
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
 
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
 
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
 
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
---
 
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
 
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:
 
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))))
 
Back
Top