Counting Unique Values in a Named Range using COUNTIF

**arsone** · 02-20-2012, 08:02 AM

Currently trying to search for unique client ID #s across a large database with non-contiguous fields. I created a named range to hold all of the client ID fields. Currently playing with these two formulas, but I keep getting a #VALUE! error.

Option 1: =SUMPRODUCT((clientid<>"")/COUNTIF(clientid,clientid&""))

Option 2: =SUMPRODUCT(1/COUNTIF(CLIENT_ID,CLIENT_ID))

The only ideas I've got left get real messy real quick. Anyone know of any straightforward forumlas I can apply to this situation? Let me know. Thanks.

**Fotis1991** · 02-20-2012, 08:16 AM

Hi and welcome to the forum.

Your first formula, works ok for me.

[B]=SUMPRODUCT((clientid<>"")/COUNTIF(clientid,clientid&""))[/B

If you still have problem with this, pls upload a small sample workbook.

**arsone** · 02-20-2012, 08:23 AM

Hmm. Still pulling an error. Fairly certain it's a careless mistake at this point, but I've been staring at it for close to three hours now. Workbook attached - thanks for taking a look.

ActiveClients.xlsx

**Fotis1991** · 02-20-2012, 09:08 AM

...As you understand your Named range, is not something regular....

My suggesttion is to copy all your values to ONE column and then use the formula!!

**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!

**Fotis1991** · 02-21-2012, 11:11 AM

You are welcome

!

So, pls mark your thread, as SOLVED!

Counting Unique Values in a Named Range using COUNTIF

LinkBack

Thread Tools

Rate This Thread

Display

Counting Unique Values in a Named Range using COUNTIF

Re: Counting Unique Values in a Named Range using COUNTIF

Re: Counting Unique Values in a Named Range using COUNTIF

Re: Counting Unique Values in a Named Range using COUNTIF

Re: Counting Unique Values in a Named Range using COUNTIF

Re: Counting Unique Values in a Named Range using COUNTIF

Re: Counting Unique Values in a Named Range using COUNTIF

Re: Counting Unique Values in a Named Range using COUNTIF

Re: Counting Unique Values in a Named Range using COUNTIF

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions