I’ve got an extremely short but valuable Excel tip for you
today: how to count the number of unique values in a range. Sometimes you need
to count values in a row or column but not if they repeat. To count only the
unique numbers or words in Excel, use the following formulas depending on:
If there are no blank cells:
=SUMPRODUCT(1/COUNTIF(Range,
Range))
With or without blank cells:
=SUMPRODUCT((Range<>"")/COUNTIF(Range,Range&""))
Replace Range with A1:A7 for example.
COUNTIF is probably
the function you’re most used to that will count based on a given condition.
SUMPRODUCT returns the sum of the product in the range. SUMPRODUCT functions as an array
formula, you just don't have to enter it as such.
To see how this
formula works step by step, click on the cell that contains the formula, then
go to Formula tab, and click Evaluate Formula and you can cycle through each
step in the calculation.
For more, see the index of Excel tips page.
Love your templates you have created, we have a template we use for our nascar pool and was wondering if you could make it better and easier to work with it.
ReplyDelete