Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting single instances in a column
Got a table of projects few thousands of rows. There is a column for target language (EN, DE, ES etc.) and there is a column with translator name. How do I get number of translators who work into one specific language? Wahur -- wahur ------------------------------------------------------------------------ wahur's Profile: http://www.excelforum.com/member.php...o&userid=26555 View this thread: http://www.excelforum.com/showthread...hreadid=559269 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting single instances in a column
Have a look at Dcount or COuntif - one or the other should do what you want.
HTH Sheila www.c-i-m-s.com MS Office training, London "wahur" wrote: Got a table of projects few thousands of rows. There is a column for target language (EN, DE, ES etc.) and there is a column with translator name. How do I get number of translators who work into one specific language? Wahur -- wahur ------------------------------------------------------------------------ wahur's Profile: http://www.excelforum.com/member.php...o&userid=26555 View this thread: http://www.excelforum.com/showthread...hreadid=559269 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting single instances in a column
Sheila D Wrote: Have a look at Dcount or COuntif - one or the other should do what you want. HTH DCOUNT does not work - it only counts cells with numbers - I have text DCOUNTA does count instances of language - telling me I have x projects done into that language COUNTIF does the same as COUNTA unless you can provide some clever trick that I do not know. Vahur -- wahur ------------------------------------------------------------------------ wahur's Profile: http://www.excelforum.com/member.php...o&userid=26555 View this thread: http://www.excelforum.com/showthread...hreadid=559269 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting single instances in a column
Can you provide a sample of your data, along with expected results?
In article , wahur wrote: DCOUNT does not work - it only counts cells with numbers - I have text DCOUNTA does count instances of language - telling me I have x projects done into that language COUNTIF does the same as COUNTA unless you can provide some clever trick that I do not know. Vahur |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting single instances in a column
EN John EN Jack EN John EN Anne FR Peter FR John I want to get that I've got 3 translators for EN and 2 translators for FR It does not have to be one formula, e.g I can autofilter by language or sort them by lang and copy by lang to other sheets. To use IT terminology, I need to find out how many unique strings are in a column. Vahur -- wahur ------------------------------------------------------------------------ wahur's Profile: http://www.excelforum.com/member.php...o&userid=26555 View this thread: http://www.excelforum.com/showthread...hreadid=559269 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting single instances in a column
Assuming that A2:B7 contains the data, let D2 contain EN, and D3 contain
FR, then try the following... [Option 1] Download and and install the free add-in Morefunc.xll, then try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... E2, copied down: =COUNTDIFF(IF(A$2:A$7=D2,IF(B$2:B$7<"",B$2:B$7)), ,FALSE) [Option 2] E2, copied down: =COUNT(1/FREQUENCY(IF(A$2:A$7=D2,IF(B$2:B$7<"",MATCH(B$2:B $7,B$2:B$7,0)) ),ROW(B$2:B$7)-ROW(B$2)+1)) ....which also needs to be confirmed with CONTROL+SHIFT+ENTER. Note that Option 1 is much more efficient. Hope this helps! In article , wahur wrote: EN John EN Jack EN John EN Anne FR Peter FR John I want to get that I've got 3 translators for EN and 2 translators for FR It does not have to be one formula, e.g I can autofilter by language or sort them by lang and copy by lang to other sheets. To use IT terminology, I need to find out how many unique strings are in a column. Vahur |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy column range of "single word" cells with spaces to a single c | Excel Discussion (Misc queries) | |||
Creating a single vertical array from multiple column arrays | Excel Worksheet Functions | |||
Counting Data in a Column | Excel Worksheet Functions | |||
match and count words | Excel Worksheet Functions | |||
Change the width of a single column in a column chart | Charts and Charting in Excel |