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
...
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" wrote in message
...
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" wrote in message

...

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