Excel has many tools for data analysis, but some simple metrics about the elements of a list are surprisingly awkward to get. That’s why the EXTRACT group of the !SEMTools add-in has a Statistical metrics (Count) section. It calculates several metrics for the elements of the selected range in a couple of clicks, without formulas. Below, each metric with the equivalent formula, so you can compare.
Number of occurrences of an element in a range
This tool shows how many times each element occurs in the selected range. It’s useful for finding the most frequent values and analyzing the distribution of the data. More examples, including the occurrence number and a case-sensitive count, are in the article on how to count repeated values in Excel.
| Element | Occurrences |
|---|---|
| A | 3 |
| B | 2 |
| C | 1 |
| A | 3 |
| B | 2 |
| A | 3 |
The formula with the COUNTIF function, with the elements in A1:A6:
=COUNTIF($A$1:$A$6,A1)
For a list of unique values with their counts, see how to count unique values in Excel.
Occurrence number of an element
This tool shows which occurrence of the element this is: how many times it has already appeared above, plus 1. It helps track the order in which elements appear, and the first occurrences (1) are exactly the unique values.
| Element | Occurrence number |
|---|---|
| A | 1 |
| B | 1 |
| C | 1 |
| A | 2 |
| B | 2 |
| A | 3 |
=COUNTIF($A$1:A1,A1)
Note the reference: only the start is fixed, so the range grows as you fill the formula down.
Share of the total (numbers only)
This tool calculates the share of each value in the total of all numbers in the range: the contribution of each element.
| Element | Value | Share of the total |
|---|---|---|
| A | 10 | 10% |
| B | 20 | 20% |
| C | 30 | 30% |
| A | 10 | 10% |
| B | 20 | 20% |
| A | 10 | 10% |
=B1/SUM($B$1:$B$6)
Apply the Percentage format to the result.
Cumulative share of the total
This tool calculates the share of each element in the total on a running basis: the running total divided by the grand total. It shows how the contribution grows as elements accumulate.
| Element | Value | Running total | Cumulative share |
|---|---|---|---|
| A | 10 | 10 | 10% |
| B | 20 | 30 | 30% |
| C | 30 | 60 | 60% |
| A | 10 | 70 | 70% |
| B | 20 | 90 | 90% |
| A | 10 | 100 | 100% |
The running total and the cumulative share:
=SUM($B$1:B1)
=SUM($B$1:B1)/SUM($B$1:$B$6)
More about it in the article on cumulative share in Excel.
ABC analysis
ABC analysis splits elements into three categories, A, B and C, by their importance, based on the cumulative share. More about the method and the procedure in the article on ABC analysis in Excel.
The Statistical metrics group keeps growing, and new tools for data analysis are planned. All of them return values right in place or into the next column, without formulas to type and fill down.
Count occurrences, shares, cumulative shares and ABC classes for whole columns in a couple of clicks, without formulas.
!SEMTools solves this and hundreds of other tasks right in Excel!
Summary
I hope you found what you were looking for on this page. COUNTIF covers occurrences, a simple division covers shares, and SUM with a growing range covers running totals. For regular analysis of long lists, the !SEMTools procedures calculate all these metrics in place.