# Trying to count unique values for one column when another column is equal to a cell

1. ## Trying to count unique values for one column when another column is equal to a cell

Hello! A quick run-down...I have claim numbers in column A, procedure codes in column B and units in column C. I've pulled out the unique procedure codes and put them in column E and now need to know the count of unique claim numbers in column A where column B equals the (variable) procedure codes in G. I have to be missing something, b/c I can't imagine this is as difficult as I'm making it.

untitled.JPG

Any help is GREATLY appreciated!

2. ## Re: Trying to count unique values for one column when another column is equal to a cell

Try this in cell F2:

=SUM((\$B\$2:\$B\$6=E2)*(IFERROR(MATCH(\$A\$2:\$A\$6,INDEX(IF(\$B\$1:\$B\$6=E2,\$A\$1:\$A\$6),0),0),0)=ROW(\$A\$2:\$A\$6)))

Confirmed with Ctrl-Shift-Enter, not just Enter.

So, pretty difficult

3. ## Re: Trying to count unique values for one column when another column is equal to a cell

YES!!! That is perfect! Thank you SO MUCH! I've been driving myself batty thinking I was missing something simple!

4. ## Re: Trying to count unique values for one column when another column is equal to a cell

Here's another one...

Array entered** in F2 and copied down:

=SUM(IF(FREQUENCY(IF(B\$2:B\$6=E2,MATCH(A\$2:A\$6,A\$2:A\$6,0)),ROW(A\$2:A\$6)-ROW(A\$2)+1),1))

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

5. ## Re: Trying to count unique values for one column when another column is equal to a cell

Thanks, Tony! That one worked as well!

Do either of you know which of these two formulas processes fastest? I'm working with a little over 37K lines total. Trying to scale down as much as possible before using the array, but sheesh! It's still taking forever to run.

Thank you again for the help!

6. ## Re: Trying to count unique values for one column when another column is equal to a cell

In which case I'd be tempted to use a couple of helper columns, rather than complicated array formula.

Can you post a sample workbook? It's a bit of a pain having to retype your data.

7. ## Re: Trying to count unique values for one column when another column is equal to a cell

Sorry about that. I've attached a sample, Andrew. I'm a newbie here, so please let me know if I didn't do it correctly.

8. ## Re: Trying to count unique values for one column when another column is equal to a cell

OK, right back at you ... I've added a hidden column (D) which is, effectively, counting unique values.

This makes all of the formula much simpler (and, therefore, faster). You'll have to try it against your full data set to see how it does.

9. ## Re: Trying to count unique values for one column when another column is equal to a cell

Aha! Brilliant! And yes, this speeds the process along significantly. You're amazing! Have a wonderful weekend!

10. ## Re: Trying to count unique values for one column when another column is equal to a cell

@andrew-r, for some reason, you are getting 2 for 58552, whereas i am getting 1 for the same. when i manually check the data, 1 seems to be the right answer. also, because of concatenation, if you were to have blanks in column A, then the results would be incorrect.

here is another approach...

11. ## Re: Trying to count unique values for one column when another column is equal to a cell

Andrew - Rats! I spoke too soon. In spotchecks, it isn't completely holding up. Just needs tweaking, thought, I think, and definitely a better solution than an array, given the size of the data.

icestationzbra - Thanks! I'll have a look!

I really appreciate everyone's help! Wish this forum had been around when I FIRST started using Excel.

12. ## Re: Trying to count unique values for one column when another column is equal to a cell

Originally Posted by michbek
Thanks, Tony! That one worked as well!

Do either of you know which of these two formulas processes fastest? I'm working with a little over 37K lines total. Trying to scale down as much as possible before using the array, but sheesh! It's still taking forever to run.

Thank you again for the help!
I would have to test the formulas in the actual file.

If you're interested there is calculation timer code here.

In general, doing calculations on unique entries in large datasets is calculation intensive.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1