Excel 2007: Formula to count unique values in a range

[David Lipetz]

Using Excel 2007, I am trying to find a formula that will enable me to count
unique values in a range.

For instance:

CAB1
CAB1
CAB2
CAB2
CAB3
CAB4

The formula would evaluate the range and return a value of 4 (there are 4
unique values in the range). There are hundreds of possible values so I can
not evaluate the range for each value. While a pivot table would work, I am
looking for a solution using a formula, if possible, so that I can use the
results programmatically.

Thanks,
David

 

[Bob Umlas]

As long as there are no blanks in the range:
Ctrl+Shift+Enter:
=SUM(1/COUNTIF(A1:A6,A1:A6))

 

[Shane Devenshire]

Hi,

I'm a big fan of the suggestion Bob gave you.

If you don't want to array enter it you can use

=SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6))

and if there can be blanks in the range which you don't want to count:

=SUMPRODUCT((A1:A6<>"")/COUNTIF(A1:A6,A1:A6&""))

 

[Domenic]

Here's another way...

=SUM(IF(FREQUENCY(IF(A2:A100<>"",MATCH("~"&A2:A100,A2:A100&"",0)),ROW(A2:
A100)-ROW(A2)+1),1))

....confirmed with CONTROL+SHIFT+ENTER. Adjust the range, accordingly.

 

[David Lipetz]

Thanks for the suggestions. I should have pointed out in my original post
that I really need to use a SUMPRODUCT type of formula as there will be
several other parameters to check for:

1) Doc Type = Invoice
2) Specific Year
3) Specific Month
4) Doc Class <> (exclude)
5) Transaction Type
6) Line Item Class
THEN count the number of unique values that meet that criteria.

The working formula I use to total the transaction is:

=(SUMPRODUCT(--(Status="Normal"),--(YEAR(Date)=$C11),--(MONTH(Date)=MONTH($B11)),--(Class<>"BS"),--(Type="Misc"),--(Class=G$3),(Net)))

Rather than the sum of NET, I want to count unique values of named range
SOP.

I tried the formual below, but the result is 0 and takes forever to
calculate.

=(SUMPRODUCT(--(Status="Normal"),--(YEAR(Date)=$C10),--(MONTH(Date)=MONTH($B10)),--(Class<>"BS"),--(Type="Misc"),--(Class=J$3),(1/COUNTIF(SOP,SOP))))

Ideas?

David

 

[David Lipetz]

And another important note, the values in range SOP are not integers.

 

[Lori Miller]

I think Domenic's array-formula can be shortened slightly to:

=COUNT(1/(MATCH("~"&A2:A100,A2:A100&T(1/(A2:A100<>""))
,0)=ROW(A2:A100)-ROW(A2)+1))

or with named range SOP and entered normally:

=COUNT(INDEX(1/(MATCH("~"&SOP,INDEX(SOP&T(1/(SOP<>""))
,),0)=ROW(SOP)-MIN(ROW(SOP))+1),))

Maybe then try adding extra conditions in place of &T(1/(SOP<>"")):

....&T(1/(SOP<>"")/(Status="Normal")/(YEAR(Date)=$C11)
/(MONTH(Date)=MONTH($B11))/(Class<>"BS")/(Type="Misc"))...

HTH. Lori

 

[Bernd P]

Hello David,

I suggest to use a Pfreq(Pfreq(...)) approach if you do not have too
many rows:
http://sulprobil.com/html/pfreq.html

If you have too many rows for this, a worksheet function approach is
likely fail as well:
http://sulprobil.com/html/count_unique.html

[In this case adjust Pfreq for your individual needs.]

Regards,
Bernd

 

[David Lipetz]

Bernd,

I am evaluating as many as 27K rows.

Thank you for the GREAT tip on COUNTU! I have installed the user-defined
function and it does work, but I can not figure out how to use it conjuction
with a SUMPRODUCT or other formula used to limit the range that COUNTU will
evaluate.

For instance, I want to look at transaction records for July 2009 and
determine the number of unique customer records (each customer may make
multiple purchases, I want to know how many customers purchased in a given
time period).

Named ranges are:

Date=H_DATE
Customer Number=H_CUSTNO

I tried the formula below (evaluating on July 2009 records) but it results
in a #VALUE! error.

=SUMPRODUCT(--(YEAR(H_Date)=2009),--(MONTH(H_Date)=7),COUNTU(H_CustNo))

What approach should I take so that I can restrict the range that COUNTU
will evaluate?

 

[Bernd P]

Hello David,

You can try to use my UDF Pstat:
http://sulprobil.com/html/pstat.html

Or you can use a pivot table.

But, to be honest, if you use 27k of rows or more, you might want to
consider a database approach.

Regards,
Bernd

 

[David Lipetz]

Thanks Bernd.

I will also use with ranges of 3K or less.

Is there no way to use COUNTU in a SUMPRODUCT formula? No way to limit range
it evaluates?

Pivot table will work but obviously requires manual setup, prefer a formula.

Will look at UDF Pstat.

Thanks,
David

 

[David Lipetz]

Will PSTAT work in conjunction with COUNTU?

Unless it does, I do not see how this helps. I need to count unique entries
in a list of given number or string combinations with a condition applied.

David