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)
| A | B | |
|---|---|---|
| 1 | Element | Occurrences |
| 2 | apple | 3=COUNTIF($A$2:$A$7,A2) |
| 3 | pear | 2=COUNTIF($A$2:$A$7,A3) |
| 4 | apple | 3=COUNTIF($A$2:$A$7,A4) |
| 5 | plum | 1=COUNTIF($A$2:$A$7,A5) |
| 6 | pear | 2=COUNTIF($A$2:$A$7,A6) |
| 7 | apple | 3=COUNTIF($A$2:$A$7,A7) |
Fully fixed range $A$2:$A$7: every row counts across the whole list. apple occurs 3 times, pear 2, plum 1.
For a list of unique values with their counts, see how to count unique values in Excel.
In !SEMTools: EXTRACT → Count → Element count in range. Letter case is ignored.
Occurrence number of an element in a list
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)
| A | B | |
|---|---|---|
| 1 | Element | Occurrence # |
| 2 | apple | 1=COUNTIF($A$2:A2,A2) |
| 3 | pear | 1=COUNTIF($A$2:A3,A3) |
| 4 | apple | 2=COUNTIF($A$2:A4,A4) |
| 5 | plum | 1=COUNTIF($A$2:A5,A5) |
| 6 | pear | 2=COUNTIF($A$2:A6,A6) |
| 7 | apple | 3=COUNTIF($A$2:A7,A7) |
Only the start is fixed, so the range grows: the third apple (A7) gets 3. Filter by 1 to get the unique values.
Note the reference: only the start is fixed, so the range grows as you fill the formula down.
In !SEMTools: EXTRACT → Count → Occurence number.
Share of each number in the total
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.
In !SEMTools: EXTRACT → Count → Share from sum (numbers).
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)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Share | Running | Cum. share |
| 2 | North | 400 | 40%=B2/SUM($B$2:$B$5) | 400=SUM($B$2:B2) | 40%=SUM($B$2:B2)/SUM($B$2:$B$5) |
| 3 | South | 250 | 25%=B3/SUM($B$2:$B$5) | 650=SUM($B$2:B3) | 65%=SUM($B$2:B3)/SUM($B$2:$B$5) |
| 4 | East | 200 | 20%=B4/SUM($B$2:$B$5) | 850=SUM($B$2:B4) | 85%=SUM($B$2:B4)/SUM($B$2:$B$5) |
| 5 | West | 150 | 15%=B5/SUM($B$2:$B$5) | 1000=SUM($B$2:B5) | 100%=SUM($B$2:B5)/SUM($B$2:$B$5) |
Share: each value divided by the total of 1,000. Running total and cumulative share grow row by row; the last row is always 100%.
More about it in the article on cumulative share in Excel.
In !SEMTools: EXTRACT → Count → Accumulated share.
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.
In !SEMTools: EXTRACT → Count → ABC-analysis (color cells).
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: statistical metrics without formulas
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.