Count of unique identifiers based on multiple criteria from different columns

[sairb]

I am trying to get a count of unique identifiers based on variables from two different columns. In addition, in one of those columns, I need to add multiple additional criteria. For example:

Column A Column B Column C
NJ Joe A
NJ John B
NY Mike A
CA Dave C
NJ Dan A
NJ Joe A
NJ Joe D
NY Pete A

What I am looking for is if Column A= ”NJ” or “NY” and Column C=A, then count unique names in Column B. In my example above, the total unique count would be 4 (Joe from NJ, Mike from NY, Dan from NJ and Pete from NY). The formula that I have been experimenting is this:
{=sum(--(frequency(if((C2:C8=”A”)*(A2:A8=”NJ”),B2:B8),B2:B8)>0))}
This formula would work if I only needed to incorporate one variable from column A, but I cannot seem to figure out how to add multiple criteria from column A.

Any help is greatly appreciated.
Thanks,

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]NJ[/TD]
[TD]Joe[/TD]
[TD]A[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]NJ[/TD]
[TD]John[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]NY[/TD]
[TD]Mike[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]CA[/TD]
[TD]Dave[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]NJ[/TD]
[TD]Dan[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]NJ[/TD]
[TD]Joe[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]NJ[/TD]
[TD]Joe[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]NY[/TD]
[TD]Pete[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

D1=SUM(IF(FREQUENCY(IF(A1:A8<>A4,IF(C1:C8=C1,MATCH(B1:B8,B1:B8,0))),ROW(B1:B8)-ROW(B1)+1),1)) Control+Shift+Enter

 

[sairb]

Thank you for the quick response Marziotullio. I see your formula is excluding A4 to bring in just NY and NJ. Suppose i have all 50 states and I just want to grab the ones I need. Is there a way to modify the formula after A1:A8 to extract just "NJ" and "NY" and all other states that i will eventually need? I want to be able to add up to 10 or so states that will be in the formula to calculate my unique name counts. My real life worksheet has codes and not states but for simplicity i used states in my example.

 

[MARZIOTULLIO]

Try

D1=SUM(IF(FREQUENCY(IF((A1:A8=A1)+(A1:A8=A3),IF(C1:C8=C1,MATCH(B1:B8,B1:B8,0))),ROW(B1:B8)-ROW(B1)+1),1)) control+shift+enter

 

[sairb]

Like a Boss!!!! Worked perfectly. Thank you so much. Gonna save me alot of time.