References...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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:
 
Column Q1 thru Q4 should contain the abbreviated country (FRA, GBR, ...)
In colum R1, enter formula:
=COUNTIF(A:A,"*("&Q1&")")

HTH
 
Back
Top