Countif for Unique Values with a second range

[helpme20]

What is the proper way to write the countif formula if I have Column A being A1:A685 with different customer names and In Column B1:B685 you have the Sales Reps name that handles the account.

Column B might contain about 8 different sales reps names.

Can someone tell me the proper way to write this formula?

 

[Steve=True]

If you have Excel 2007, you would want to use Countifs formula.

Type

=Countifs(

then click on the Fx button in the formula bar or hit Shift+f3 and a popup box will appear and it will walk you through it.

 

[Aladin Akyurek]

What is the proper way to write the countif formula if I have Column A being A1:A685 with different customer names and In Column B1:B685 you have the Sales Reps name that handles the account.

Column B might contain about 8 different sales reps names.

Can someone tell me the proper way to write this formula?

Are you trying to determine (count) how many distinct customers are per sales rep?

 

[helpme20]

Are you trying to determine (count) how many distinct customers are per sales rep?

Yes, using 2007 and trying to count the unique/distinct customers each rep has.

 

[Aladin Akyurek]

Yes, using 2007 and trying to count the unique/distinct customers each rep has.

Let...

C1: Mark
C2: Jon
Etc...


D1, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$1:$A$685<>"",IF($B$1:$B$685=C1,
    MATCH($A$1:$A$685,$A$1:$A$685,0))),
     ROW($A$1:$A$685)-ROW($A$1)+1),1))

 

[T. Valko]

What is the proper way to write the countif formula if I have Column A being A1:A685 with different customer names and In Column B1:B685 you have the Sales Reps name that handles the account.

Column B might contain about 8 different sales reps names.

Can someone tell me the proper way to write this formula?

Try this...

Book1
	A	B	C	D	E
2	Customer2	Rep1	_	Rep1	2
3	Customer8	Rep1	_	Rep2	3
4	Customer8	Rep1	_	Rep3	1
5	Customer1	Rep2	_	Rep4	0
6	Customer5	Rep2	_	Rep5	3
7	Customer9	Rep2	_	Rep6	3
8	Customer10	Rep3	_	Rep7	1
9	Customer10	Rep3	_	Rep8	4
10	Customer3	Rep5	_	_	_
11	Customer8	Rep5	_	_	_
12	Customer10	Rep5	_	_	_
13	Customer3	Rep6	_	_	_
14	Customer4	Rep6	_	_	_
15	Customer7	Rep6	_	_	_
16	Customer9	Rep7	_	_	_
17	Customer3	Rep8	_	_	_
18	Customer5	Rep8	_	_	_
19	Customer6	Rep8	_	_	_
20	Customer8	Rep8	_	_	_
Sheet1

This array formula** entered in E2 and copied down:

=SUM(IF(FREQUENCY(IF(B$2:B$20=D2,MATCH(A$2:A$20,A$2:A$20,0)),ROW(A$2:A$20)-ROW(A$2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes no empty cells within the customer range of column A.

 

[wakerider017]

Old post, but this worked perfect. Thanks!

 

[Aladin Akyurek]

Old post, but this worked perfect. Thanks!

You are welcome.