A couple of weeks ago I published a video where I showed how to count unique values. At the start of that video I had a screenshot that displayed the names of the cities for each country in a single cell and a count of the number of items in the cell.
Since I published the video I’ve had a couple of people emailing me asking how I generated the cities-in-a-single-cell and the count of items in the cell.
This week’s YouTube video provides the answers.
The formulas I use in the tutorial:
=TEXTJOIN(",",TRUE,UNIQUE(FILTER($B$2:$B$13,$C$2:$C$13=F2)))
=COUNTA(TEXTSPLIT(J2,", "))
TEXTJOIN
J2 contains the TEXTJOIN function. This combines into a single string the result of the UNIQUE(FILTER($B$2:$B$13,$C$2:$C$13=F2)) function.
The UNIQUE/FILTER function generates a unique list of cities (from column B) where the country (column C) matches France (F2).
The first argument of the TEXTJOIN function is the separator string (known as a delimiter) which in this example is comma-space, hence each city having a comma and space between them.
The second argument of the TEXTJOIN function is TRUE which means ignore any empty cells in the range to be combined (column B in this example).
TEXTJOIN is available in Excel 2016 and later. UNIQUE and FILTER are available in Excel 365 and Excel 2021 and later
COUNTA and TEXTSPLIT
H2 counts the number of items in J2. This is done by combining TEXTSPLIT and COUNTA.
TEXTSPLIT is used to split the contents of a single cell based on a delimiter which in this example is comma-space. What this function is actually doing is splitting the text string in column J into multiple items based on comma-space but unlike Text-to-Columns it isn’t physically storing the “broken-apart” string anywhere.
COUNTA counts the number of items generated by TEXTSPLIT.
COUNTA is available in all versions of Excel. TEXTSPLIT is available in Excel 365 and Excel 2021 and later
Download a copy of the file used in this video: https://share.zight.com/RBuRm1rg
With COUNTA TEXTSPLIT How do you stop the formula counting 1 instead of zero where there is no text entered?
for example I have a master sheet with cells counting the multiple assets counted in one cell from other sheets, the formula I have entered is =COUNTA(TEXTSPLIT(CONT01!B13,”, “))
This works where I have say for asset numbers entered = 4, but when none entered instead of zero or staying blank it says 1
Hi Bec. Try this: =IF(CONT01!B13 =””,COUNTA(TEXTSPLIT(CONT01!B13,”, “))-1,COUNTA(TEXTSPLIT(CONT01!B13,”, “)))
Hi Mike
This might sound like a silly question but how could one count the total number of comma separated values in an entire column? There are many tutorials about counting the number of comma separated values in a single cell but can this idea be extended to counting the number of comma separated values in a range of cells ie a number of cells that could be an entire column or even more than one column. ie LEN(A2)-LEN(SUBSTITUTE(A2,”,”,””))+1 will count the number of values separated by column in a single cell but there are at least 2 problems with applying this formula to a range of cells such as a column. 1 Where there are no vlaues in a cell a count of 1 1 will be returned when it should be zero and 2 the #Spill error is shown. I’m looking at hashtags in a column and was hoping to count the total number of hashtags in a column but this has not be as easy as it looks.
Hi Terry. Not a silly question at all. I think I was about it once before. There are several solutions. Here’s the one I used (assumes you have your data down col A and B
=SUMPRODUCT((A1:B1000<>“”) * (LEN(A1:B1000) – LEN(SUBSTITUTE(A1:B1000, “,”, “”)) + 1))