“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 COUNTIFs 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.