Counting Unique names only, based on a couple of criteria

[dixiebritt]

Quarter	SR	Client
q1	jane	abc company
q1	jane	mcg inc
q1	jane	mcg inc
q1	house	box llc
q1	bob	hti
q2	bob	hti
q2	bob	apple
q2	jane	abc company

<tbody>
</tbody>

I have this set of data. I want to do two things:
1. calculate the number of total unique clients by Quarter and SR
2. calculate the number of total unique clients by Quarter only

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I	J	K
1	Quarter	SR	Client		Quarter	SR	# Unique Clients		Quarter	# Unique Clients
2	q1	jane	abc company		q1	Jane	2		q2	4
3	q1	jane	mcg inc
4	q1	jane	mcg inc
5	q1	house	box llc
6	q1	bob	hti
7	q2	bob	hti
8	q2	bob	apple
9	q2	jane	abc company

<tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula G2 {=SUM(SIGN(FREQUENCY(IF($A$2:$A$9=E2,IF($B$2:$B$9=F2,MATCH($C$2:$C$9,$C$2:$C$9,0))),ROW($C$2:$C$9)-ROW($C$2)+1)))} J2 {=SUM(SIGN(FREQUENCY(IF($A$2:$A$9=I2,MATCH($C$2:$C$9,$C$2:$C$9,0)),ROW($C$2:$C$9)-ROW($C$2)+1)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Marcelo Branco]

Eric,

The formula in J2 seems perfect to me but i think it should return 3, not 4 - maybe you have pasted the wrong cell.

M.

 

[dixiebritt]

When I do this it is only yielding "1" as my answer?

 

[dixiebritt]

I noticed that as well, but when I run this formula it yields "1"

 

[Marcelo Branco]

I noticed that as well, but when I run this formula it yields "1"

Worked perfectly for me.
Have you confirmed the formula with Ctrl+Shift+Enter simultaneously?

M.

 

[dixiebritt]

yes, I will try again,.

 

[Marcelo Branco]

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Quarter​	SR​	Client​		Quarter​	SR​	# Unique Clients​		Quarter​	# Unique Clients​
2​	q1​	jane​	abc company​		q1​	Jane​	2​		q2​	3​
3​	q1​	jane​	mcg inc​
4​	q1​	jane​	mcg inc​
5​	q1​	house​	box llc​
6​	q1​	bob​	hti​
7​	q2​	bob​	hti​
8​	q2​	bob​	apple​
9​	q2​	jane​	abc company​
10​

<tbody>
</tbody>

Array formulas in G2 and J2 provided by Eric in post 2.

M.

 

[Eric W]

Good catch Marcelo. After I posted the formulas, I noticed that the J2 formula was pointing to the wrong cell, so I fixed it. But I didn't fix the result cell, so that's why it shows incorrectly. Thanks for the assist.