Counting Unique Values in a Named Range using COUNTIF

**WHER** · 02-20-2012, 11:22 AM

If a UDF is acceptable see attachment, cell C100;

**arsone** · 02-21-2012, 07:34 AM

I think fotis1991 is right. I was trying to find a way to avoid putting everything in one column because my employer wanted everything visually segregated by month. But unless I start building pivot tables, I think this is the best way to do it. Added a column for "month" to sort.

One remaining problem: using the first function

=SUMPRODUCT((CLIENT_ID<>"")/COUNTIF(CLIENT_ID,CLIENT_ID&""))

yields a #DIV/0 error when the range includes blank cells. I can't figure out why - shouldn't the ("CLIENT_ID<>"") part preclude blank cells from the function? I tried updating the function to

=SUMPRODUCT((CLIENT_ID<>"")/COUNTIF(CLIENT_ID,IF(CLIENT_ID=0,0,CLIENT_ID)))

but I'm still getting a zero error. Thoughts? Thanks guys!

**Fotis1991** · 02-21-2012, 08:06 AM

Hi

..yields a #DIV/0 error when the range includes blank cells. I can't figure out why - shouldn't the ("CLIENT_ID<>"") part preclude blank cells from the function?

Why you say this??

Maybe am i missing something??

**arsone** · 02-21-2012, 11:04 AM

Strange. When I save it as an '.xls' file, the function breaks and I get a zero error. When I save it as an '.xslx' file, it works. Does .xls not support named ranges? Using Excel for Mac 2008.

Doesn't matter, it's working now. Thanks everybody for the assist!