Count Unique Values
 

I'm trying to count unique values based on muliple criteria. I tried using
the formula
=SUMPRODUCT(--(B2:B2500=B2),--(D2:D2500="HY")/COUNTIF(F2:F2500,F2:F2500)) but
when there's a blank in column "F" I get an error. Can this formula be writen
to ignore blank cells?

Thank you in advance for your help
Joe

Count Unique Values
 

Try this:

=SUM(N(FREQUENCY(IF((B2:B2500="a")*(D2:D2500="HY") ,MATCH(F2:F2500&"",F2:F2500&"",0)),MATCH(F2:F2500& "",F2:F2500&"",0))0))

ctrl+shift+enter, not just enter


"Joe Gieder" wrote:

I'm trying to count unique values based on muliple criteria. I tried using
the formula
=SUMPRODUCT(--(B2:B2500=B2),--(D2:D2500="HY")/COUNTIF(F2:F2500,F2:F2500)) but
when there's a blank in column "F" I get an error. Can this formula be writen
to ignore blank cells?

Thank you in advance for your help
Joe

Count Unique Values
 

That will return an incorrect result *if* column F is empty while columns B
and D meet the criteria.

Try it like this:

Array entered** :

=SUM(N(FREQUENCY(IF((B2:B2500="a")*(D2:D2500="HY") *(F2:F2500<""),MATCH(F2:F2500&"",F2:F2500&"",0)), ROW(F2:F2500)-ROW(F2)+1)0))

That also calculates about twice as fast.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Try this:

=SUM(N(FREQUENCY(IF((B2:B2500="a")*(D2:D2500="HY") ,MATCH(F2:F2500&"",F2:F2500&"",0)),MATCH(F2:F2500& "",F2:F2500&"",0))0))

ctrl+shift+enter, not just enter


"Joe Gieder" wrote:

I'm trying to count unique values based on muliple criteria. I tried
using
the formula
=SUMPRODUCT(--(B2:B2500=B2),--(D2:D2500="HY")/COUNTIF(F2:F2500,F2:F2500))
but
when there's a blank in column "F" I get an error. Can this formula be
writen
to ignore blank cells?

Thank you in advance for your help
Joe

Count Unique Values
 

I had posted a similar question but did not get a useable answer until I saw
your solution and when I tried it, it worked beautifully. Just wanted to say
that your formula helped me to find an answer that I had spent 2 days looking
for.
THANK YOU!!!!!!

"T. Valko" wrote:

That will return an incorrect result *if* column F is empty while columns B
and D meet the criteria.

Try it like this:

Array entered** :

=SUM(N(FREQUENCY(IF((B2:B2500="a")*(D2:D2500="HY") *(F2:F2500<""),MATCH(F2:F2500&"",F2:F2500&"",0)), ROW(F2:F2500)-ROW(F2)+1)0))

That also calculates about twice as fast.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Try this:

=SUM(N(FREQUENCY(IF((B2:B2500="a")*(D2:D2500="HY") ,MATCH(F2:F2500&"",F2:F2500&"",0)),MATCH(F2:F2500& "",F2:F2500&"",0))0))

ctrl+shift+enter, not just enter


"Joe Gieder" wrote:

I'm trying to count unique values based on muliple criteria. I tried
using
the formula
=SUMPRODUCT(--(B2:B2500=B2),--(D2:D2500="HY")/COUNTIF(F2:F2500,F2:F2500))
but
when there's a blank in column "F" I get an error. Can this formula be
writen
to ignore blank cells?

Thank you in advance for your help
Joe

Count Unique Values
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Excel-User-RR" wrote in message
...
I had posted a similar question but did not get a useable answer until I
saw
your solution and when I tried it, it worked beautifully. Just wanted to
say
that your formula helped me to find an answer that I had spent 2 days
looking
for.
THANK YOU!!!!!!

"T. Valko" wrote:

That will return an incorrect result *if* column F is empty while columns
B
and D meet the criteria.

Try it like this:

Array entered** :

=SUM(N(FREQUENCY(IF((B2:B2500="a")*(D2:D2500="HY") *(F2:F2500<""),MATCH(F2:F2500&"",F2:F2500&"",0)), ROW(F2:F2500)-ROW(F2)+1)0))

That also calculates about twice as fast.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
Try this:

=SUM(N(FREQUENCY(IF((B2:B2500="a")*(D2:D2500="HY") ,MATCH(F2:F2500&"",F2:F2500&"",0)),MATCH(F2:F2500& "",F2:F2500&"",0))0))

ctrl+shift+enter, not just enter


"Joe Gieder" wrote:

I'm trying to count unique values based on muliple criteria. I tried
using
the formula
=SUMPRODUCT(--(B2:B2500=B2),--(D2:D2500="HY")/COUNTIF(F2:F2500,F2:F2500))
but
when there's a blank in column "F" I get an error. Can this formula be
writen
to ignore blank cells?

Thank you in advance for your help
Joe