Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
References...
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
References...
Column Q1 thru Q4 should contain the abbreviated country (FRA, GBR, ...)
In colum R1, enter formula: =COUNTIF(A:A,"*("&Q1&")") HTH -- AP "fak119" a ιcrit dans le message de news: ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing cell references in formulas to names and back again. | Excel Discussion (Misc queries) | |||
Cell Reference's when Pasting | Excel Discussion (Misc queries) | |||
Automatically Changing Cell Reference's when Pasting in Excel | Excel Discussion (Misc queries) | |||
More- AutoFill with Non-Seqeuntial Cell References ? | Excel Worksheet Functions | |||
Absolute cell references and subsequent problems. | Excel Discussion (Misc queries) |