I love it when I can learn a new thing in software program.
I have had to do this function many times before, however, I have never run across this option: “Frequency”.
| A | B | C | D |
| 1 | Purchased | Colors | |
| 2 | Green | Green | |
| 3 | Yellow | ||
| 4 | Green | ||
| 5 | Red |
=frequency(B2:B10,D2) Results = 2
Counts how many times in B2 thru B10 that match what is in D2 – Awesome.
so, let’s add a little bit to this (I love things that do multiple things at one time (multi-tasking!) )
IF… you wanted to know how many unique items are in a column:
=SUM( — (FREQUENCY(B2:B5,B2:B5)>0))
— = Double-unary: A single unary operator (-) coerces true/false values into -1/0. By using the double unary operaor, we coerce the values again to 1/0.
This can be used in place of the countif statement for large sets of data.
