hi

if I have to named ranges of cell
range 1 has for example the list of countries that adopted the euro as
currency
in the range 2 i have the list of countries that are member of the
European Union.

Is there a formula like
count those countries in the range2 that belong also to range 1
?
thanks

=COUNTA(range1)

If it has the Euro as its currency, it is de facto a member of the union is
it not?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"lorenzo" <(E-Mail Removed)> wrote in message
news:3d93f2b6-d4da-4947-acf6-(E-Mail Removed)...
> hi
>
> if I have to named ranges of cell
> range 1 has for example the list of countries that adopted the euro as
> currency
> in the range 2 i have the list of countries that are member of the
> European Union.
>
> Is there a formula like
> count those countries in the range2 that belong also to range 1
> ?
> thanks

thanks for the effort

1- not all the member of the EU use the euro (UK is a clear example)
2- let's make it general: i have a list1 (shorter) and a list2
(longer): which is the formula i can use tho determine how many
element of list2 are also in list1?

this is somehow a followup of my question of last night, if i dont
fail you also replied

thanks!



On Aug 14, 11:53 am, "Bob Phillips" <bob....@somewhere.com> wrote:
> =COUNTA(range1)
>
> If it has the Euro as its currency, it is de facto a member of the union is
> it not?
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "lorenzo" <lcn...@email.it> wrote in message
>
> news:3d93f2b6-d4da-4947-acf6-(E-Mail Removed)...
>
> > hi
>
> > if I have to named ranges of cell
> > range 1 has for example the list of countries that adopted the euro as
> > currency
> > in the range 2 i have the list of countries that are member of the
> > European Union.
>
> > Is there a formula like
> > count those countries in the range2 that belong also to range 1
> > ?
> > thanks

Hi Lorenzo

=SUMPRODUCT(--(COUNTIF(range2,range1)))

--
Regards
Roger Govier

"lorenzo" <(E-Mail Removed)> wrote in message
news:3d93f2b6-d4da-4947-acf6-(E-Mail Removed)...
> hi
>
> if I have to named ranges of cell
> range 1 has for example the list of countries that adopted the euro as
> currency
> in the range 2 i have the list of countries that are member of the
> European Union.
>
> Is there a formula like
> count those countries in the range2 that belong also to range 1
> ?
> thanks

thanks really a lot.

Could you explain me why this formula works? i dont get it
the result of the COUNTIF seems to be an error
and I do not understand what you do with the SUMPRODUCT

and also i dont understand what are those "--" you have after the
first parenthesis

now, going back to the question of last night
imagine i have a database with all the countries of the world
and then have a series of clusters (lists with a bunch of country
names)

i'd like to sumproduct column 7 and column 9 of my database
but only for those countries of the world that belong to cluster1 or
cluster5

how do i do it?

thanks really a lot!


On Aug 14, 12:12 pm, "Roger Govier"
<roger@technology4unospamdotcodotuk> wrote:
> Hi Lorenzo
>
> =SUMPRODUCT(--(COUNTIF(range2,range1)))
>
> --
> Regards
> Roger Govier
>
> "lorenzo" <lcn...@email.it> wrote in message
>
> news:3d93f2b6-d4da-4947-acf6-(E-Mail Removed)...
>
> > hi
>
> > if I have to named ranges of cell
> > range 1 has for example the list of countries that adopted the euro as
> > currency
> > in the range 2 i have the list of countries that are member of the
> > European Union.
>
> > Is there a formula like
> > count those countries in the range2 that belong also to range 1
> > ?
> > thanks

Hi Lorenzo

I set up a small list of countries called range1 and a larger list called
range2.
Sumproduct cannot use whole columns as ranges (unless it is Xl2007), so
don't use complete columns as ranges.

When you use Sumproduct, it passes the range 1 as an array to to the larger
range, and would be like having
=COUNTIF(B1:B100,{"France","Germany","Italy",......"Belgium"})
which will either be 1 or 0 for each country within the larger list
depending on whether it is found or not.
Sumproduct then adds all these 1's and 0's to give the total number.

The -- is not strictly necessary in this case, as Countif is returning
numeric values.
However, I am so use to using Sumproduct where there are text comparisons
involved, which return either True or False.
The -- or double unary minus, coerces those True's to 1's and Falsie's to
0's so they can be added by Sumproduct.
--
Regards
Roger Govier

"lorenzo" <(E-Mail Removed)> wrote in message
news:47eff657-db67-4a95-aa64-(E-Mail Removed)...
> thanks really a lot.
>
> Could you explain me why this formula works? i dont get it
> the result of the COUNTIF seems to be an error
> and I do not understand what you do with the SUMPRODUCT
>
> and also i dont understand what are those "--" you have after the
> first parenthesis
>
> now, going back to the question of last night
> imagine i have a database with all the countries of the world
> and then have a series of clusters (lists with a bunch of country
> names)
>
> i'd like to sumproduct column 7 and column 9 of my database
> but only for those countries of the world that belong to cluster1 or
> cluster5
>
> how do i do it?
>
> thanks really a lot!
>
>
> On Aug 14, 12:12 pm, "Roger Govier"
> <roger@technology4unospamdotcodotuk> wrote:
>> Hi Lorenzo
>>
>> =SUMPRODUCT(--(COUNTIF(range2,range1)))
>>
>> --
>> Regards
>> Roger Govier
>>
>> "lorenzo" <lcn...@email.it> wrote in message
>>
>> news:3d93f2b6-d4da-4947-acf6-(E-Mail Removed)...
>>
>> > hi
>>
>> > if I have to named ranges of cell
>> > range 1 has for example the list of countries that adopted the euro as
>> > currency
>> > in the range 2 i have the list of countries that are member of the
>> > European Union.
>>
>> > Is there a formula like
>> > count those countries in the range2 that belong also to range 1
>> > ?
>> > thanks
>

That is not what I said. I am well aware that the UK doesn't use the Euro, I
personally spend pounds sterling.

You asked for ... Is there a formula like count those countries in the
range2 (the countries of the EU) that belong also to range 1 (countries
having adopted the Euro).

I said .....If it has the Euro as its currency, it is de facto a member of
the union is it not?

That is, the countries in range 1 are the list that you want, so just count
range1 QED

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"lorenzo" <(E-Mail Removed)> wrote in message
news:ef9f51a4-1619-4bdb-9cd3-(E-Mail Removed)...
> thanks for the effort
>
> 1- not all the member of the EU use the euro (UK is a clear example)
> 2- let's make it general: i have a list1 (shorter) and a list2
> (longer): which is the formula i can use tho determine how many
> element of list2 are also in list1?
>
> this is somehow a followup of my question of last night, if i dont
> fail you also replied
>
> thanks!
>
>
>
> On Aug 14, 11:53 am, "Bob Phillips" <bob....@somewhere.com> wrote:
>> =COUNTA(range1)
>>
>> If it has the Euro as its currency, it is de facto a member of the union
>> is
>> it not?
>>
>> --
>> HTH
>>
>> Bob
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>> "lorenzo" <lcn...@email.it> wrote in message
>>
>> news:3d93f2b6-d4da-4947-acf6-(E-Mail Removed)...
>>
>> > hi
>>
>> > if I have to named ranges of cell
>> > range 1 has for example the list of countries that adopted the euro as
>> > currency
>> > in the range 2 i have the list of countries that are member of the
>> > European Union.
>>
>> > Is there a formula like
>> > count those countries in the range2 that belong also to range 1
>> > ?
>> > thanks
>

Roger,

Can't you see it either.

Range1 - Germany, France, Italy

Range2 - Germany, France, Italy, UK, Denmark

=SUMPRODUCT(--(COUNTIF(range2,range1))) result 3

=COUNTA(range1) result 3

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message
news:62186D39-A7EE-4927-A676-(E-Mail Removed)...
> Hi Lorenzo
>
> I set up a small list of countries called range1 and a larger list called
> range2.
> Sumproduct cannot use whole columns as ranges (unless it is Xl2007), so
> don't use complete columns as ranges.
>
> When you use Sumproduct, it passes the range 1 as an array to to the
> larger range, and would be like having
> =COUNTIF(B1:B100,{"France","Germany","Italy",......"Belgium"})
> which will either be 1 or 0 for each country within the larger list
> depending on whether it is found or not.
> Sumproduct then adds all these 1's and 0's to give the total number.
>
> The -- is not strictly necessary in this case, as Countif is returning
> numeric values.
> However, I am so use to using Sumproduct where there are text comparisons
> involved, which return either True or False.
> The -- or double unary minus, coerces those True's to 1's and Falsie's to
> 0's so they can be added by Sumproduct.
> --
> Regards
> Roger Govier
>
> "lorenzo" <(E-Mail Removed)> wrote in message
> news:47eff657-db67-4a95-aa64-(E-Mail Removed)...
>> thanks really a lot.
>>
>> Could you explain me why this formula works? i dont get it
>> the result of the COUNTIF seems to be an error
>> and I do not understand what you do with the SUMPRODUCT
>>
>> and also i dont understand what are those "--" you have after the
>> first parenthesis
>>
>> now, going back to the question of last night
>> imagine i have a database with all the countries of the world
>> and then have a series of clusters (lists with a bunch of country
>> names)
>>
>> i'd like to sumproduct column 7 and column 9 of my database
>> but only for those countries of the world that belong to cluster1 or
>> cluster5
>>
>> how do i do it?
>>
>> thanks really a lot!
>>
>>
>> On Aug 14, 12:12 pm, "Roger Govier"
>> <roger@technology4unospamdotcodotuk> wrote:
>>> Hi Lorenzo
>>>
>>> =SUMPRODUCT(--(COUNTIF(range2,range1)))
>>>
>>> --
>>> Regards
>>> Roger Govier
>>>
>>> "lorenzo" <lcn...@email.it> wrote in message
>>>
>>> news:3d93f2b6-d4da-4947-acf6-(E-Mail Removed)...
>>>
>>> > hi
>>>
>>> > if I have to named ranges of cell
>>> > range 1 has for example the list of countries that adopted the euro as
>>> > currency
>>> > in the range 2 i have the list of countries that are member of the
>>> > European Union.
>>>
>>> > Is there a formula like
>>> > count those countries in the range2 that belong also to range 1
>>> > ?
>>> > thanks
>>

you're right, i apologize.

that's the reason i like to make it more general ("list_long"
"list_short")

On Aug 15, 11:11 am, "Bob Phillips" <bob....@somewhere.com> wrote:
> That is not what I said. I am well aware that the UK doesn't use the Euro, I
> personally spend pounds sterling.
>

I am a bit lost here. What do you refer to? what is "result3"?

Could you please tell me how to sumproduct for example column3 and
column 7 only for those country in column1 ("world_list") that belong
to "short_list"?



Aug 15, 11:11 am, "Bob Phillips" <bob....@somewhere.com> wrote:
> Roger,
>
> Can't you see it either.
>
> Range1 - Germany, France, Italy
>
> Range2 - Germany, France, Italy, UK, Denmark
>
> =SUMPRODUCT(--(COUNTIF(range2,range1))) result 3
>
> =COUNTA(range1) result 3
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message
>
> news:62186D39-A7EE-4927-A676-(E-Mail Removed)...
>
> > Hi Lorenzo
>
> > I set up a small list of countries called range1 and a larger list called
> > range2.
> > Sumproduct cannot use whole columns as ranges (unless it is Xl2007), so
> > don't use complete columns as ranges.
>
> > When you use Sumproduct, it passes the range 1 as an array to to the
> > larger range, and would be like having
> > =COUNTIF(B1:B100,{"France","Germany","Italy",......"Belgium"})
> > which will either be 1 or 0 for each country within the larger list
> > depending on whether it is found or not.
> > Sumproduct then adds all these 1's and 0's to give the total number.
>
> > The -- is not strictly necessary in this case, as Countif is returning
> > numeric values.
> > However, I am so use to using Sumproduct where there are text comparisons
> > involved, which return either True or False.
> > The -- or double unary minus, coerces those True's to 1's and Falsie's to
> > 0's so they can be added by Sumproduct.
> > --
> > Regards
> > Roger Govier
>
> > "lorenzo" <lcn...@email.it> wrote in message
> >news:47eff657-db67-4a95-aa64-(E-Mail Removed)...
> >> thanks really a lot.
>
> >> Could you explain me why this formula works? i dont get it
> >> the result of the COUNTIF seems to be an error
> >> and I do not understand what you do with the SUMPRODUCT
>
> >> and also i dont understand what are those "--" you have after the
> >> first parenthesis
>
> >> now, going back to the question of last night
> >> imagine i have a database with all the countries of the world
> >> and then have a series of clusters (lists with a bunch of country
> >> names)
>
> >> i'd like to sumproduct column 7 and column 9 of my database
> >> but only for those countries of the world that belong to cluster1 or
> >> cluster5
>
> >> how do i do it?
>
> >> thanks really a lot!
>
> >> On Aug 14, 12:12 pm, "Roger Govier"
> >> <roger@technology4unospamdotcodotuk> wrote:
> >>> Hi Lorenzo
>
> >>> =SUMPRODUCT(--(COUNTIF(range2,range1)))
>
> >>> --
> >>> Regards
> >>> Roger Govier
>
> >>> "lorenzo" <lcn...@email.it> wrote in message
>
> >>>news:3d93f2b6-d4da-4947-acf6-(E-Mail Removed)...
>
> >>> > hi
>
> >>> > if I have to named ranges of cell
> >>> > range 1 has for example the list of countries that adopted the euro as
> >>> > currency
> >>> > in the range 2 i have the list of countries that are member of the
> >>> > European Union.
>
> >>> > Is there a formula like
> >>> > count those countries in the range2 that belong also to range 1
> >>> > ?
> >>> > thanks