Conditional weighted average for all but one in a range

[tblackwell]

Trying to get a weighted average of sales for all employees except "7407 - NPX". In the example I have shown the long way to do this, but looking for a different way because the employee names and positions frequently switch in the table.

I tried:

SUMPRODUCT(--($A$15:$A$25<>A2),$C$15:$C$25,$B$15:$B$25)/SUMIF($A$15:$A$25,<>A2,$B$15:$B$25)

The SUMPRODUCT forumula works, the SUMIF does not. Can you help?

Book1
	A	B	C
1	Weighted average for all but:
2	7407 - NPX
3
4
5
6
7
8
9
10
11
12
13
14	Employee	# clients	sales
15	70 - HOLLAND,	1	$121,470
16	447 - SIDDOWAY	1	$109,803
17	462 - DRAKE,	2	$165,039
18	551 - TAGGART,	3	$108,615
19	960 - BENDER,	2	$182,537
20	1496 - DROLLINGER,	1	$193,258
21	1715 - TURNER,	18	$126,305
22	4701 - OLSEN,	1	$117,345
23	5186 - PUMPHREY,	6	$108,229
24	7407 - NPX	52	$111,348
25	12186 - BYU	0	$0
26
27
28	Expected Result:		$131,933
29
30	Formula tried:		SUMPRODUCT(--($A$15:$A$25<>A2),$C$15:$C$25,$B$15:$B$25)/SUMIF($A$15:$A$25,<>A2,$B$15:$B$25)
Sheet1

Cell Formulas
Range	Formula
C28	C28	=SUM(C$15*$B$15,C$16*B$16,C$17*$B$17,C$18*$B$18,C$19*$B$19,C$20*$B$20,C$21*$B$21,C$22*$B$22,C$23*$B$23,C$25*$B$25)/SUM($B$15,$B$17,$B$18,$B$19,$B$20,$B$21,$B$22,$B$23,$B$25)

 

[Snakehips]

@tblackwell Amend the SUMIF condition parameter of your formula
SUMPRODUCT(--($A$15:$A$25<>A2),$C$15:$C$25,$B$15:$B$25)/SUMIF($A$15:$A$25,"<>" &A2,$B$15:$B$25)

Also, I think you will find that your Expected Result above is incorrect as you have missed $B$16 from the SUM

Hope that helps.

 

[tblackwell]

And....that worked! Thank you @Snakehips. I was close but you sealed the deal! And you were right I was missing $B$16, all the more reason to change to the new formula. Much appreciated!