Mar 07 2019 11:33 AM
I am working on a sheet that has 38,074 company names in a column, of which 2,512 are unique. How can I determine the number of times each of the 2,512 appear in order to make the total of 38,074?
Mar 07 2019 12:05 PM
Mar 07 2019 02:02 PM
I loaded the dynamic array version of Excel and already I am dreading having to revert to deliver client workbooks. I made the list of companies into a table so that the named range 'list' (of companies) becomes dynamic. I then move to my formula cell and named it 'distinct'.
The formula I inserted into the cell is
= SORT(UNIQUE(list))
which spills to provide a list of the distinct company names from 'list', sorted alphabetically. Moving across again, I name a new formula cell 'occurrences' and then insert the formula
= COUNTIFS(list, distinct#)
to get a count corresponding to each distinct company.
The number of distinct companies 'countDistinct' is given by
= COUNTA(distinct#)
and, just as a check, the values
= COUNTA(list)
= SUM(occurrences#)
are identical.
So few formulas, so many results!
Mar 07 2019 05:20 PM
Mar 08 2019 06:19 AM