References...

I have one column with names and nationalities, the latter as an abbreviation
such as “(USA)”

In a separate list I have to sum the different nationalities. Somewhat like
this.

Col. A

Miller (USA)
Forget (FRA)
Smith (GBR
Schmidt (GER)
Stone (USA)
Meier (GER)
…

(The names in this col. A may be typed in the cells or come from a formula)


The List in say, col. Q and R, should the look like this:


France 1
Germany 2
Great Britain 1
USA 2
....

The column in Q with the country names exists already and the two columns
(Q:R) should be able to be sorted.

Is there a formula (or macro) that can do the trick?

Thank you

Hi Fak

one way is create a column with the abbreviation in your spreadsheet,
between the Q ans R cols.

so it will be

Q R S

France (FRA) 1
Germany (GER) 2
Gran Britain (GBE) 1
USA (USA) 2

on the S column use the formula

=sumproduct(--(rigth($A$5:$A$100;5)=R5))

Hope it helps - thanks for the feedback
regards from Brazil
Marcelo


the formula on the

"fak119" escreveu:

> I have one column with names and nationalities, the latter as an abbreviation
> such as β€œ(USA)”
>
> In a separate list I have to sum the different nationalities. Somewhat like
> this.
>
> Col. A
>
> Miller (USA)
> Forget (FRA)
> Smith (GBR
> Schmidt (GER)
> Stone (USA)
> Meier (GER)
> …
>
> (The names in this col. A may be typed in the cells or come from a formula)
>
>
> The List in say, col. Q and R, should the look like this:
>
>
> France 1
> Germany 2
> Great Britain 1
> USA 2
> ...
>
> The column in Q with the country names exists already and the two columns
> (Q:R) should be able to be sorted.
>
> Is there a formula (or macro) that can do the trick?
>
> Thank you
>

Column Q1 thru Q4 should contain the abbreviated country (FRA, GBR, ...)
In colum R1, enter formula:
=COUNTIF(A:A,"*("&Q1&")")

HTH
--
AP

"fak119" <[email protected]> a ιcrit dans le message de news:
[email protected]...
>I have one column with names and nationalities, the latter as an
>abbreviation
> such as "(USA)"
>
> In a separate list I have to sum the different nationalities. Somewhat
> like
> this.
>
> Col. A
>
> Miller (USA)
> Forget (FRA)
> Smith (GBR
> Schmidt (GER)
> Stone (USA)
> Meier (GER)
> .
>
> (The names in this col. A may be typed in the cells or come from a
> formula)
>
>
> The List in say, col. Q and R, should the look like this:
>
>
> France 1
> Germany 2
> Great Britain 1
> USA 2
> ...
>
> The column in Q with the country names exists already and the two columns
> (Q:R) should be able to be sorted.
>
> Is there a formula (or macro) that can do the trick?
>
> Thank you
>