CODEWITHSUNDEEP
excelvba
phentrus
phentrus

Reputation: 29

Find all unique values in column then count occurances

I have a column in excel with many different model types. There are about 100. Each occurs randomly a different amount of time in the column. I have found a similar question but it seems like I need to list all unique values first. As of now I am filtering and counting the total.

I would like the output to look something like

Model   |       Occurance

A |99

B| 47

C|55

A link that had a similar question is here: Distinct value of a column and do a count on each distinct value yet it is not a perfect fit.

Upvotes: 0

Views: 1073

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

Let your Model data be at A2:A100 range and your Occurance data be at B2:B100 range

1) You can use following array formula (I've got it from this book) to find unique values (note that it works only if your data doesn't contains empty rows). Select destination range (for example F2:F100) and then enter the following formula in the formula bar:

=IFERROR(INDEX($A$2:$A$100,SMALL(IF(MATCH($A$2:$A$100,$A$2:$A$100,0)=ROW(INDIRECT("1:" & ROWS($A$2:$A$100))),MATCH($A$2:$A$100,$A$2:$A$100,0),""),ROW(INDIRECT("1:" & ROWS($A$2:$A$100))))),"")

and press CTRL+SHIFT+ENTER to evaluate it.

2) next step, write following formula in G2 and stretch it down until G100 :

=IF(F2<>"",SUMIF($A$2:$A$100,F2,$B$2:$B$100),"")

it will give you total occurance for each value

Upvotes: 1

Related Questions