Counting Unique if theres a value in another field

[creative999]

I want to count how many unique Sale IDs there are:

a. if there is a value in the Rev Share column.
and separatly,
b. the Sale ID has NO Rev Share

So in the attached example:
a. = 3
b. = 2

Any suggestions:

Book2
	A	B
1	REV SHARE	SALE ID
2	ABC	a9e
3		a9e
4	ABC	a9e
5	XYZ	a9e
6		a9e
7		64M
8		64M
9		64M
10		64M
11		VXq
12	DEF	VXq
13		VXq
14	DEF	VXq
15		VXq
16	ABC	7Qe
17	ABC	7Qe
18		7Qe
19		7Qe
20	DEF	7Qe
21		7Qe
22		Xp2
23		Xp2
24		Xp2
25		Xp2
26		Xp2
Sheet1

 

[Marcelo Branco]

Try

Pasta1
	A	B	C	D	E
1	REV SHARE	SALE ID		Question a	Question b
2	ABC	a9e		3	2
3		a9e
4	ABC	a9e
5	XYZ	a9e
6		a9e
7		64M
8		64M
9		64M
10		64M
11		VXq
12	DEF	VXq
13		VXq
14	DEF	VXq
15		VXq
16	ABC	7Qe
17	ABC	7Qe
18		7Qe
19		7Qe
20	DEF	7Qe
21		7Qe
22		Xp2
23		Xp2
24		Xp2
25		Xp2
26		Xp2
Plan3

Cell Formulas
Range	Formula
D2	D2	=SUM(IF(FREQUENCY(IF(A2:A26<>"",MATCH(B2:B26,B2:B26,0)),ROW(B2:B26)-ROW(B2)+1),1))
E2	E2	=SUM(IF(FREQUENCY(IF(COUNTIFS(A2:A26,"<>",B2:B26,B2:B26)=0,MATCH(B2:B26,B2:B26,0)),ROW(B2:B26)-ROW(B2)+1),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

M.

 

[Marcelo Branco]

Question b - a simpler formula

Pasta1
	D	E
1	Question a	Question b
2	3	2
Plan3

Cell Formulas
Range	Formula
D2	D2	=SUM(IF(FREQUENCY(IF(A2:A26<>"",MATCH(B2:B26,B2:B26,0)),ROW(B2:B26)-ROW(B2)+1),1))
E2	E2	=SUM(IF(FREQUENCY(MATCH(B2:B26,B2:B26,0),ROW(B2:B26)-ROW(B2)+1),1))-D2
Press CTRL+SHIFT+ENTER to enter array formulas.

M.

 

[Eric W]

With Excel 365:

Book1
	A	B	C	D	E
1	REV SHARE	SALE ID		Question A	Question B
2	ABC	a9e		3	5
3		a9e
4	ABC	a9e
5	XYZ	a9e
6		a9e
7		64M
8		64M
9		64M
10		64M
11		VXq
12	DEF	VXq
13		VXq
14	DEF	VXq
15		VXq
16	ABC	7Qe
17	ABC	7Qe
18		7Qe
19		7Qe
20	DEF	7Qe
21		7Qe
22		Xp2
23		Xp2
24		Xp2
25		Xp2
26		Xp2
Sheet14

Cell Formulas
Range	Formula
D2	D2	=ROWS(UNIQUE(FILTER(B2:B26,(A2:A26<>""))))
E2	E2	=ROWS(UNIQUE(FILTER(B2:B26,(A2:A26=""))))

For question B, there are 5 unique Sale IDs with a blank Rev Share. Did you mean excluding ones already found, like Marcelo's formula? If so, just subtract D2.

 

[creative999]

All worked a treat.... now to toss a coin a choose one
Thank you so much all

 

[Marcelo Branco]

You're welcome. Glad we could help.

M.

 

[creative999]

Seems, i also need to check another column for any value, for example:

a. if there is a value in the Rev Share column and there is a value in Col C

 

[Marcelo Branco]

Seems, i also need to check another column for any value, for example:

A data sample along with expected results would be helpful

M.

 

[creative999]

A data sample along with expected results would be helpful

M.

The new formula / count here should = 2 (those in red)

Based on the earlier requirement to count unique SALE ID where there is a REV SHARE and INCLUSIVE value.

Book2
	A	B	C
1	REV SHARE	SALE ID	INCLUSIVE
2	ABC	a9e	YES
3		a9e	YES
4	ABC	a9e
5	XYZ	a9e
6		a9e	YES
7		64M
8		64M	YES
9		64M
10		64M
11		VXq
12	DEF	VXq
13		VXq	YES
14	DEF	VXq
15		VXq
16	ABC	7Qe	YES
17	ABC	7Qe
18		7Qe
19		7Qe
20	DEF	7Qe
21		7Qe	YES
22		Xp2
23		Xp2
24		Xp2	YES
25		Xp2
26		Xp2
Sheet1

 

[Eric W]

The Excel 365 way:

Book1
	A	B	C	D
1	REV SHARE	SALE ID	INCLUSIVE	Question C
2	ABC	a9e	YES	2
3		a9e	YES
4	ABC	a9e
5	XYZ	a9e
6		a9e	YES
7		64M
8		64M	YES
9		64M
10		64M
11		VXq
12	DEF	VXq
13		VXq	YES
14	DEF	VXq
15		VXq
16	ABC	7Qe	YES
17	ABC	7Qe
18		7Qe
19		7Qe
20	DEF	7Qe
21		7Qe	YES
22		Xp2
23		Xp2
24		Xp2	YES
25		Xp2
26		Xp2
Sheet14

Cell Formulas
Range	Formula
D2	D2	=ROWS(UNIQUE(FILTER(B2:B26,(A2:A26<>"")*(C2:C26="YES"))))

I was also thinking about the versions A and B from the earlier post. Subtracting D2 from the E2 formula really only works if the two lists overlap. If there's some non-overlapping values, it may not give the expected results.