Count unique occurences in column A if column B equals value

Hello, I am trying to figure out how to count the unique occurrences in column A based on the value in column B. In the example below, I am trying to count the number of unique names in each state (Illinois=1, Colorado=2, New York=2). I would like the formula to count unique occurrences for the entire column A because I will be adding to the list. I feel like it should be somewhat simple, but it is driving me crazy. Any help would be appreciated


..A.............B............C
John......Illinois
John.....Illinois
John.....Colorado
Alex.....Colorado
Alex.....New York
Steve...New York
Steve...New York

You can use the Remove Duplicates function. That will tell you how many duplicates were removed and the number of remaining unique entries.

Unfortunately the data that spreadsheet is on is going to be provided each week and each is a separate case. I need to write a formula, without VBA, that will count the unique occurrences if column B is a certain value.


I have tried SUM(IF(FREQUENCY())) which I believe is the right way to go, but I cant figure out how to structure it

Okay, I'm really sorry I feel like that should work but I cant seem to make it work. I have uploaded a snapshot of the table. I tried to simplify my example by changing the context so maybe that is part of the issue.

I am trying to count unique occurrences of column A (SA/A 1, SA/A 2, SA/A 3 and SA/A 4) for each region (AMS, APJ, EMEA and USPS). So AMS=1, APJ=2, EMEA=1 and USPS=1...

If you could give it a shot I would REALLY appreciate it

Here is my attempt - please note, I am trying to reference the whole column because the list will be added to.

Let me know if you see where I am going wrong...

I am dam sure what is happening in this formula, i have just correct the range D to d16
The value is 1 which is expected as per you .check with any other typical
use ctrl+Shift+Enter
=SUM(IF(FREQUENCY(IF(DATA!D$2:$D$16=A2,MATCH(DATA!A$2:$A$16,DATA!A$2:$A$16,0)),ROW(DATA!D$2:$D$16)-ROW(DATA!$D$2:$D$16)+1),1))
Punnam

Thank you, that is working for me but is it possible to use $D$2:$D and $A$2:$A in order to reference the whole column besides the column label/title?

I am getting an error when, but the list is going to be constantly added to by multiple people so I don't want to have to constantly update the formula

hi,

Have checked the correctness of the formula by adding any additional data fields ?
First do the check,

i have updated range as u asked o do if for entire column.

Punnam

Yeah, for some reason on the version you just uploaded the calculation for APJ=1 but it should be 2 (SA/A 2 and SA/A 3 in cells A8 and A9 ...do you know how to fix that?

Also, here is my latest attempt. It works correctly for AMS but I get #VALUE for the other 3 regions