I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

COUNTIF

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.

Values:

List_unique_values

Formulas:

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. See here, please. Other examples on COUNTIF you can see at RANK and at Largest Remainder.

This worksheet function approach needs a runtime of O(n^2) because of the COUNTIFs in column B. My UDFs sbUniq and Pfreq only need a runtime of O(n).

If you are interested in downloading a 408 KB Excel 2010 sample file go to my Download page, please.

List_unique_formulas
Sulprobil   Get it done   Contact   Disclaimer   Download