**“Nagging is the repetition of unpalatable truths.” [Edith Clara Summerskill]**

## Abstract

If you cannot use VBA or if you want to use only worksheet functions you can count
frequencies of numbers or strings as follows: First you create a list of unique entries of a table.
Then you apply *COUNTIF*’s on these.

Example: Suppose A1:A10000 contain 10,000 random characters from A thru H. With two helper columns you can create a list of unique entries without array formulas:

Please note that the normal formula in E1 should have read

```
=COUNTIF(A$1:A$10000,D1)
```

I just prefer to count or to sum conditionally with *SUMPRODUCT* because the number of
conditions might get increased - and then it might be impossible to use *COUNTIF* any longer:

This worksheet function approach requires a runtime of O(n^2) because of the *COUNTIF*s
in column B. My UDFs *sbCountUniq* and *sbMiniPivot* only need a runtime of O(n).
Countif is a nice function as long as you need to apply only one criterion.
If you want to count how often the value “5” appears in a data set, for example, you can use

```
=COUNTIF(Dataset,”=5”) or:
```

But as soon as you face two or more conditions which you want to apply with
logical AND’s or OR’s, you are in trouble with *COUNTIF*.

*SUMPRODUCT* is your solution here:

Please note that I do NOT suggest to look at *SUMPRODUCT* as a holy grail.
I use this example just as an evolutionary argument.
Please read additional material on *SUMPRODUCT* here.