I have a column of data that looks something like this:

Spanish
0
0
French
0
German
French
0
0
Spanish

I want to insert a formula at the bottom of the column to give me the total
number of unique languages (that is, a count of all the unique values,
ignoring zeros).

I have tried using both of the following formulas (which I don't really
understand so feel free to point out the obvious), but both include the zeros
in the count:

=SUMPRODUCT((D3:D77<"")/COUNTIF(D3:D77,D3:D77&""))

AND

=SUM(IF(FREQUENCY(MATCH(D3:D77,D3:D77,0),MATCH(D3: D77,D3:D77,0))0,1))

where D3:D77 is the range of data I am working with.

The reason I have zeros in my column is that the cells already contain an IF
formula which populates with the language name if column B contains a 1 and
populates with a 0 otherwise, i.e:
=IF(B3=1,"Spanish",0)

Can anyone help?

=SUM(1/COUNTIF(A1:A10,A1:A10))-1
and confirm as an array formula (use CNTRL-SHFT-ENTER rather than just ENTER)

will display 3 for your example.
--
Gary''s Student


"doublew" wrote:

I have a column of data that looks something like this:

Spanish
0
0
French
0
German
French
0
0
Spanish

I want to insert a formula at the bottom of the column to give me the total
number of unique languages (that is, a count of all the unique values,
ignoring zeros).

I have tried using both of the following formulas (which I don't really
understand so feel free to point out the obvious), but both include the zeros
in the count:

=SUMPRODUCT((D3:D77<"")/COUNTIF(D3:D77,D3:D77&""))

AND

=SUM(IF(FREQUENCY(MATCH(D3:D77,D3:D77,0),MATCH(D3: D77,D3:D77,0))0,1))

where D3:D77 is the range of data I am working with.

The reason I have zeros in my column is that the cells already contain an IF
formula which populates with the language name if column B contains a 1 and
populates with a 0 otherwise, i.e:
=IF(B3=1,"Spanish",0)

Can anyone help?

That worked - thanks!

"Gary''s Student" wrote:

=SUM(1/COUNTIF(A1:A10,A1:A10))-1
and confirm as an array formula (use CNTRL-SHFT-ENTER rather than just ENTER)

will display 3 for your example.
--
Gary''s Student


"doublew" wrote:

I have a column of data that looks something like this:

Spanish
0
0
French
0
German
French
0
0
Spanish

I want to insert a formula at the bottom of the column to give me the total
number of unique languages (that is, a count of all the unique values,
ignoring zeros).

I have tried using both of the following formulas (which I don't really
understand so feel free to point out the obvious), but both include the zeros
in the count:

=SUMPRODUCT((D3:D77<"")/COUNTIF(D3:D77,D3:D77&""))

AND

=SUM(IF(FREQUENCY(MATCH(D3:D77,D3:D77,0),MATCH(D3: D77,D3:D77,0))0,1))

where D3:D77 is the range of data I am working with.

The reason I have zeros in my column is that the cells already contain an IF
formula which populates with the language name if column B contains a 1 and
populates with a 0 otherwise, i.e:
=IF(B3=1,"Spanish",0)

Can anyone help?

You are very welcome!
Thanks for the feedback
--
Gary's Student


"doublew" wrote:

That worked - thanks!

"Gary''s Student" wrote:

=SUM(1/COUNTIF(A1:A10,A1:A10))-1
and confirm as an array formula (use CNTRL-SHFT-ENTER rather than just ENTER)

will display 3 for your example.
--
Gary''s Student


"doublew" wrote:

I have a column of data that looks something like this:

Spanish
0
0
French
0
German
French
0
0
Spanish

I want to insert a formula at the bottom of the column to give me the total
number of unique languages (that is, a count of all the unique values,
ignoring zeros).

I have tried using both of the following formulas (which I don't really
understand so feel free to point out the obvious), but both include the zeros
in the count:

=SUMPRODUCT((D3:D77<"")/COUNTIF(D3:D77,D3:D77&""))

AND

=SUM(IF(FREQUENCY(MATCH(D3:D77,D3:D77,0),MATCH(D3: D77,D3:D77,0))0,1))

where D3:D77 is the range of data I am working with.

The reason I have zeros in my column is that the cells already contain an IF
formula which populates with the language name if column B contains a 1 and
populates with a 0 otherwise, i.e:
=IF(B3=1,"Spanish",0)

Can anyone help?

I now have a new problem!

I am now categorising my languages by class (according to translation rates)
and have two columns which look something like this:

A B

Spanish Class 1
French Class 1
Czech Class 2
French Class 1
Hindi Class 3

What I want to do is count how many Class 1 languages there are, how many
Class 2 and how many Class 3. But, I don't want the totals to include
duplicate languages. If I run a simple COUNTIF formula on column B, it will
tell me that there are 3 Class 1 languages, when in actual fact there are
only 2 as French is repeated.

How can I do this using formulas? I don't want to use a pivot table because
this data isn't fixed. It changes according to what the user has previously
entered, and I want this to form a tool that people can pick up and change to
run different scenarios.

"doublew" wrote:

I have a column of data that looks something like this:

Spanish
0
0
French
0
German
French
0
0
Spanish

I want to insert a formula at the bottom of the column to give me the total
number of unique languages (that is, a count of all the unique values,
ignoring zeros).

I have tried using both of the following formulas (which I don't really
understand so feel free to point out the obvious), but both include the zeros
in the count:

=SUMPRODUCT((D3:D77<"")/COUNTIF(D3:D77,D3:D77&""))

AND

=SUM(IF(FREQUENCY(MATCH(D3:D77,D3:D77,0),MATCH(D3: D77,D3:D77,0))0,1))

where D3:D77 is the range of data I am working with.

The reason I have zeros in my column is that the cells already contain an IF
formula which populates with the language name if column B contains a 1 and
populates with a 0 otherwise, i.e:
=IF(B3=1,"Spanish",0)

Can anyone help?

Say you enter the Class number you're looking to count in C1,
then try this *array* formula:

=COUNT(1/FREQUENCY(IF((B1:B5=C1),MATCH(A1:A5,A1:A5,0)),ROW( 1:5)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"doublew" wrote in message
...
I now have a new problem!

I am now categorising my languages by class (according to translation
rates)
and have two columns which look something like this:

A B

Spanish Class 1
French Class 1
Czech Class 2
French Class 1
Hindi Class 3

What I want to do is count how many Class 1 languages there are, how many
Class 2 and how many Class 3. But, I don't want the totals to include
duplicate languages. If I run a simple COUNTIF formula on column B, it
will
tell me that there are 3 Class 1 languages, when in actual fact there are
only 2 as French is repeated.

How can I do this using formulas? I don't want to use a pivot table
because
this data isn't fixed. It changes according to what the user has
previously
entered, and I want this to form a tool that people can pick up and change
to
run different scenarios.

"doublew" wrote:

I have a column of data that looks something like this:

Spanish
0
0
French
0
German
French
0
0
Spanish

I want to insert a formula at the bottom of the column to give me the
total
number of unique languages (that is, a count of all the unique values,
ignoring zeros).

I have tried using both of the following formulas (which I don't really
understand so feel free to point out the obvious), but both include the
zeros
in the count:

=SUMPRODUCT((D3:D77<"")/COUNTIF(D3:D77,D3:D77&""))

AND

=SUM(IF(FREQUENCY(MATCH(D3:D77,D3:D77,0),MATCH(D3: D77,D3:D77,0))0,1))

where D3:D77 is the range of data I am working with.

The reason I have zeros in my column is that the cells already contain an
IF
formula which populates with the language name if column B contains a 1
and
populates with a 0 otherwise, i.e:
=IF(B3=1,"Spanish",0)

Can anyone help?