I need a formula or function that will tell me how many different letters are in a column.
For example, lets say Column A looks like this:
.....A
1....A
2....B
3....C
4....C
5....-
So there are four letters in column A, but two of them are the letter "C", so there are only three different letters.
What formula or function can I put in cell A5 that will give the answer "3" (or however many are different)?
Thanks in advance!
Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (robleyd, Moderator). was I need a formula or function.
[Solved] Count distinct text in column
[Solved] Count distinct text in column
Last edited by imadumby on Thu May 27, 2021 12:52 am, edited 2 times in total.
Open Office 4.1.7, Windows 10
Re: Count distinct text in column
SUMPRODUCT() is the function you want - see X18 in [Tutorial] The SUMPRODUCT function.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Count distinct text in column
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: Count distinct text in column
To be honest, the formulas are hard to understand. It is necessary to calculate the intermediate result in steps.
In addition to @karolus answer
#1-2
Even though the FREQUENCY formula (2nd) is much longer and contains more functions than the COUNTIF formula (1st), the formula calculation time is astonishingly faster!
#3
NOTE: By itself, the COUNTIF function converts an empty cell to a zero in the criteria agrument. This results in a division by 0 error: #DIV/0! So pay attention to &"".
Empty cells will also be counted, but in the first condition, empty cells will give FALSE, and when dividing FALSE by the total number of empty cells, we will get zero. As a result, empty cells are not counted. If we don't want to treat the empty cells as one of the possible data states.
Attention! If there are empty cells in the data in formulas #1 and #2, it is necessary to handle the error #DIV/0! and #N/A, respectively, using the IFERROR function.
In addition to @karolus answer
#1-2
Code: Select all
A5:=SUMPRODUCT(1/COUNTIF(A1:A4;A1:A4))
A5:=SUMPRODUCT(FREQUENCY(MATCH(A1:A4;A1:A4;0);ROW(A1:A4)-ROW(A1)+1)>0)
#3
Code: Select all
A5:=SUMPRODUCT((A1:A4<>"")/COUNTIF(A1:A4;A1:A4&""))
Empty cells will also be counted, but in the first condition, empty cells will give FALSE, and when dividing FALSE by the total number of empty cells, we will get zero. As a result, empty cells are not counted. If we don't want to treat the empty cells as one of the possible data states.
Attention! If there are empty cells in the data in formulas #1 and #2, it is necessary to handle the error #DIV/0! and #N/A, respectively, using the IFERROR function.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
Re: SOLVED! Count distinct text in column
Thank you!
=SUMPRODUCT(1/COUNTIF($A$1:A5;$A$1:A5) worked for me right off the bat but I may give the others a try too!
Up down and done. Aint it great when things work like that!
=SUMPRODUCT(1/COUNTIF($A$1:A5;$A$1:A5) worked for me right off the bat but I may give the others a try too!
Up down and done. Aint it great when things work like that!
Open Office 4.1.7, Windows 10