Counting single instances in a column

  • Thread starter Thread starter wahur
  • Start date Start date
W

wahur

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
 
Sheila said:
Have a look at Dcount or COuntif - one or the other should do what yo
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 project
done into that language
COUNTIF does the same as COUNTA

unless you can provide some clever trick that I do not know.

Vahu
Click to expand...
 
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 fo
FR
It does not have to be one formula, e.g I can autofilter by language o
sort them by lang and copy by lang to other sheets. To use I
terminology, I need to find out how many unique strings are in
column.

Vahu
 
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!
 
Back
Top