Counting Unique names only, based on a couple of criteria

[dixiebritt]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Quarter[/TD]
[TD]SR[/TD]
[TD]Client[/TD]
[/TR]
[TR]
[TD]q1[/TD]
[TD]jane[/TD]
[TD]abc company[/TD]
[/TR]
[TR]
[TD]q1[/TD]
[TD]jane[/TD]
[TD]mcg inc[/TD]
[/TR]
[TR]
[TD]q1[/TD]
[TD]jane[/TD]
[TD]mcg inc[/TD]
[/TR]
[TR]
[TD]q1[/TD]
[TD]house[/TD]
[TD]box llc[/TD]
[/TR]
[TR]
[TD]q1[/TD]
[TD]bob[/TD]
[TD]hti[/TD]
[/TR]
[TR]
[TD]q2[/TD]
[TD]bob[/TD]
[TD]hti[/TD]
[/TR]
[TR]
[TD]q2[/TD]
[TD]bob[/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]q2[/TD]
[TD]jane[/TD]
[TD]abc company[/TD]
[/TR]
</tbody>[/TABLE]

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
Quarter	SR	Client	Quarter	SR	# Unique Clients	Quarter	# Unique Clients
q1	jane	abc company	q1	Jane	q2
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>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]{=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)))}[/TD]
[/TR]
[TR]
[TH]J2[/TH]
[TD="align: left"]{=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)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[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]

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

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Quarter​

[/TD]
[TD]

SR​

[/TD]
[TD]

Client​

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

Quarter​

[/TD]
[TD]

SR​

[/TD]
[TD]

# Unique Clients​

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

Quarter​

[/TD]
[TD]

# Unique Clients​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

q1​

[/TD]
[TD]

jane​

[/TD]
[TD]

abc company​

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

q1​

[/TD]
[TD]

Jane​

[/TD]
[TD="bgcolor: #D9D9D9"]

2​

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

q2​

[/TD]
[TD="bgcolor: #D9D9D9"]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

q1​

[/TD]
[TD]

jane​

[/TD]
[TD]

mcg inc​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

q1​

[/TD]
[TD]

jane​

[/TD]
[TD]

mcg inc​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

q1​

[/TD]
[TD]

house​

[/TD]
[TD]

box llc​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

q1​

[/TD]
[TD]

bob​

[/TD]
[TD]

hti​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

q2​

[/TD]
[TD]

bob​

[/TD]
[TD]

hti​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

q2​

[/TD]
[TD]

bob​

[/TD]
[TD]

apple​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

q2​

[/TD]
[TD]

jane​

[/TD]
[TD]

abc company​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.