Forum Discussion
Beginner Help in getting Summary - Distinct Name & No. of times used in Specific data range
excel
Trying to make a small project. I was able to get Distinct Values (B25:B29), but not able to get the number of times (Count) matching the values. Need help.
3 Replies
= LET( distinctLine, SORT(UNIQUE(Line)), count, COUNTIFS(Line, distinctLine), HSTACK(distinctLine, count) )Appreciate your help. Being novice, literal beginner in Excel, can I ask to fill in the blanks here...
=LET(
B25, SORT(UNIQUE($B$12:$B$23)),
count, COUNTIFS($B$12:$B$23, B25),
HSTACK(B25, count)
)The LET function requires that the first parameter defines the name of the variable that stores the value returned by the second parameter for further use. B25 is illegal as a name because it conflicts with the notation that is used to identify a worksheet cell. If you replace your reference by a name such as I used (both where it is defined and where it is used) then the formula should evaluate correctly to return a spilt range.
The return the result to cell B25 you must write the formula in that cell. If you write the formula somewhere else, that is where the result will be returned.
BTW: My formulas may look unfamiliar because I never use the cell location (A1 etc.) to reference data directly I always apply a Range Name that attempts to reflects the business context rather than the location on the sheet.
