• Status: Solved
• Priority: Medium
• Security: Public
• Views: 226

# Count of uniques in one column based on criteria in another column

Column B contains primarily 4-digit numbers stored as text, with the balance of rows that are either blank or have a single word in the cell (I don't have control of how this data is entered, unfortunately). Column C contains initials + date, with some rows that include "N/A" at the end, as shown below:

Column C
MN–18 Jun 14
MC–16 Jun 14 N/A
AC–13 May 14
MN–25 May 14 N/A

I would like a formula that returns a count of the # of unique 4-digit characters in Column B for all the rows where Column C doesn't not contain "N/A".

Thanks!
Andrea
--of-unique-book-IDs.xlsm
0
Andreamary
• 2
• 2
1 Solution

Commented:
Hello Andrea,

When you say "unique" I'm assuming you really mean "different", i.e. the number of different 4 digit codes

I used this formula

=SUM(IF(FREQUENCY(IF(RIGHT(C2:C1000,3)<>"N/A",IFERROR(B2:B1000+0,-1)),IFERROR(B2:B1000+0,0)),1))

confirmed with CTRL+SHIFT+ENTER

and that gave me a result of 322

regards, barry
0

Author Commented:
Hi Barry,
Thanks for the quick response. I was verifying the result of 322 by doing this manually (filtering out the n/a's in column C, then filtering out all but the 4-digit numbers in column B, copying and pasting results of Column B into a new sheet and using "Remove duplicates") and I got 321. I did it several times, always with the same result of 321. Is there a chance the heading is being included in the formula, giving the result of 322?

Andrea
0

Commented:
Yes, you're right, apologies - I tried adjusting that formula several different ways without success so try this different approach to get 321

=SUM(IF(ISNUMBER(MATCH(ROW(INDIRECT("1:10000")),IF(RIGHT(C2:C1000,3)<>"N/A",B2:B1000+0),0)),1))

still confirmed with CTRL+SHIFT+ENTER

regards, barry
0

Author Commented:
Works like a charm...thanks, Barry, much appreciated!

Cheers,
Andrea
0

## Featured Post

• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.