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

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

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

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

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

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