"COUNT IF BELONGS" formula?

  • Thread starter Thread starter lorenzo
  • Start date Start date
L

lorenzo

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)
 
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!
 
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!
 
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.
 
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)
 
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)
 
you're right, i apologize.

that's the reason i like to make it more general ("list_long"
"list_short")
 
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"?
 
Hi Bob

I see it fully, for the case of the Euro and countries of the EU.
The example he chose, the fact that the Euro is used, de facto makes them a
member of the EU, but the fact the Euro is not used, does not preclude them.

I took it that the OP chose that as an example (where your solution is quite
correct), but he really wants to apply it to other lists of countries within
a wider world country list,.
 
Back
Top