Aug 09 2022 12:46 PM
Basically I'm working only with column A, where I have several different terms, but they repeat in different amounts. I need to know how many times each of these terms appeared in column A, is there any practical way to do this? Attached just for example, but the actual list is very extense and it would take a lot of time to do it manually
Excel 2016
Aug 09 2022 02:18 PM
=COUNT(SEARCH(", "&C3&", ",", "&$A$2:$A$18&", "))
You can try this formula. Enter the formula with ctrl+shift+enter since you don't work with Office365 or 2021.
Aug 09 2022 04:45 PM - edited Aug 09 2022 04:46 PM
For some reason I can't make the formula work, it just returns #name
I'm using Office365 at home
Aug 09 2022 08:40 PM - edited Aug 09 2022 09:04 PM
SolutionHi @Bielalve
Recentily Microsoft upgrade to Excel's formula language to support dynamic arrays.
Now you cold use this simple way to do this
=UNIQUE(A2:A1812) & " " &COUNTIF(A2:A1812;UNIQUE(A2:A1812)) & "x"
or if you need filter nulable results try this other form
=LET(
rangecells;A2:A1812;
uniqList; UNIQUE(rangecells);
filteredList; FILTER(uniqList;uniqList<>"");
return; filteredList & " " &COUNTIF(rangecells;filteredList) & "x";
return
)
Hope it help you
Aug 09 2022 09:36 PM
@Bielalve Use CHOOSE() with UNIQUE() and COUNTIF() for one go. All below solution are included to attach sample file.
=CHOOSE({1,2},UNIQUE(A2:A8),COUNTIF(A2:A8,UNIQUE(A2:A8))& "x")
To show result in same cell use-
=UNIQUE(A2:A8)&" "&COUNTIF(A2:A8,UNIQUE(A2:A8))&"x"
For LAMBDA() approach to use custom function via Name Manager, try-
=LAMBDA(inRng,CHOOSE({1,2},UNIQUE(inRng),COUNTIF(inRng,UNIQUE(inRng))& "x"))(A2:A8)
Aug 10 2022 01:34 AM
Do you work with the english version of Excel? Otherwise you would have to translate the formula. You can open the attached file and the result is shown with the formula translated into your language.
Aug 10 2022 04:58 PM
Aug 09 2022 08:40 PM - edited Aug 09 2022 09:04 PM
SolutionHi @Bielalve
Recentily Microsoft upgrade to Excel's formula language to support dynamic arrays.
Now you cold use this simple way to do this
=UNIQUE(A2:A1812) & " " &COUNTIF(A2:A1812;UNIQUE(A2:A1812)) & "x"
or if you need filter nulable results try this other form
=LET(
rangecells;A2:A1812;
uniqList; UNIQUE(rangecells);
filteredList; FILTER(uniqList;uniqList<>"");
return; filteredList & " " &COUNTIF(rangecells;filteredList) & "x";
return
)
Hope it help you