Vlookup, sumif, multiple criteria
 

Hope i'll be able to explain my problem.
I have in a worksheet the following 3 -column Database (in reality is
much longer and with much more many columns):

Albania 5.50% 44
Argentina 8.77% 13
Armenia 13.70% 2
Australia 3.16% 82
Austria 2.93% 85
Azerbaijan 26.03% 1
Bahrain 7.01% 26
Barbados 4.03% 69
Belarus 7.37% 23
Belgium 2.52% 92
Belize 3.62% 79
Bolivia 4.29% 65

In another worksheet i create a column-list, that i might change,
called CLUSTER 1
in which i decide how to cluster countries.
For example i have a list that says: Albania, Austra, Bolivia,
and I also named this list "cluster1".

I am looking for a formula that gives me the sumproduct(columns B and
C). for those countries in Cluster 1.

More in general, which is the formula that allows me to say if a
country belongs or not to a range of cells?
something like
if(argentina ={Cluster1},IN,OUT)

thanks a lot

Vlookup, sumif, multiple criteria
 

Hello lorenzo,

for the first qusetion try

=SUMPRODUCT(--ISNUMBER(MATCH(A2:A100,cluster,0)),B2:B100,C2:C100 )

and for the second

=IF(COUNTIF(cluster,F2),"In","Out")

where F2 contains a specific country

"lorenzo" wrote:

Hope i'll be able to explain my problem.
I have in a worksheet the following 3 -column Database (in reality is
much longer and with much more many columns):

Albania 5.50% 44
Argentina 8.77% 13
Armenia 13.70% 2
Australia 3.16% 82
Austria 2.93% 85
Azerbaijan 26.03% 1
Bahrain 7.01% 26
Barbados 4.03% 69
Belarus 7.37% 23
Belgium 2.52% 92
Belize 3.62% 79
Bolivia 4.29% 65

In another worksheet i create a column-list, that i might change,
called CLUSTER 1
in which i decide how to cluster countries.
For example i have a list that says: Albania, Austra, Bolivia,
and I also named this list "cluster1".

I am looking for a formula that gives me the sumproduct(columns B and
C). for those countries in Cluster 1.

More in general, which is the formula that allows me to say if a
country belongs or not to a range of cells?
something like
if(argentina ={Cluster1},IN,OUT)

thanks a lot

Vlookup, sumif, multiple criteria
 

Thanks really a lot.

regarding the last point, where you have F2, how can i have the column
A of the first worksheet?
I'd like something like
Countif(cluster,world)

assuming i name "world" my entire list of countries
how to do it?
thanks


On Aug 13, 7:37 pm, daddylonglegs wrote:
Hello lorenzo,

for the first qusetion try

=SUMPRODUCT(--ISNUMBER(MATCH(A2:A100,cluster,0)),B2:B100,C2:C100 )

and for the second

=IF(COUNTIF(cluster,F2),"In","Out")

where F2 contains a specific country

"lorenzo" wrote:
Hope i'll be able to explain my problem.
I have in a worksheet the following 3 -column Database (in reality is
much longer and with much more many columns):

Albania 5.50% 44
Argentina 8.77% 13
Armenia 13.70% 2
Australia 3.16% 82
Austria 2.93% 85
Azerbaijan 26.03% 1
Bahrain 7.01% 26
Barbados 4.03% 69
Belarus 7.37% 23
Belgium 2.52% 92
Belize 3.62% 79
Bolivia 4.29% 65

In another worksheet i create a column-list, that i might change,
called CLUSTER 1
in which i decide how to cluster countries.
For example i have a list that says: Albania, Austra, Bolivia,
and I also named this list "cluster1".

I am looking for a formula that gives me the sumproduct(columns B and
C). for those countries in Cluster 1.

More in general, which is the formula that allows me to say if a
country belongs or not to a range of cells?
something like
if(argentina ={Cluster1},IN,OUT)

thanks a lot

Vlookup, sumif, multiple criteria
 

and also
what is the "--" you have after the first parenthesis?


On Aug 13, 7:37 pm, daddylonglegs wrote:
Hello lorenzo,

for the first qusetion try

=SUMPRODUCT(--ISNUMBER(MATCH(A2:A100,cluster,0)),B2:B100,C2:C100 )