Sumproduct with multiple text criteria giving an #N/A error (Weighted Average Formula)

[marklemachin]

qty	gp	salesperson	sku
1	4%	andrew	#N/A
1	4%	andrew	2
2	3%	andrew	2
3	2%	kyle	2
4	4%	kyle	#N/A

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

I want the weighted average of column 2 in regards to column 1 when column 3 equals andrew and when column 4 does not equal #N/A.

My formula is this: =SUMPRODUCT(--(C2:C6="andrew"),--(D2:D6<>"#N/A"),B2:B6,A2:A6)/SUMPRODUCT(--(C2:C6="andrew"),--(D2:D6<>"#N/A"),A2:A6)

No matter how I twist the formula I get an #N/A error

 

[marklemachin]

Nevermind I see my problem. Its because my vlookup page doesn't populate numbers....its text.

 

[marklemachin]

So now I'm back to my original problem... so for this example with the sku colum not having any numbers. I apologize for giving the wrong data before.

qty	gp	salesperson	sku
1	4%	andrew	#N/A
1	4%	andrew	crank
2	3%	andrew	smpm
3	2%	kyle	hat3
4	4%	kyle	#N/A

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

My formula is this: =SUMPRODUCT(--(C2:C6="andrew"),--(D2:D6<>"#N/A"),B2:B6,A2:A6)/SUMPRODUCT(--(C2:C6="andrew"),--(D2:D6<>"#N/A"),A2:A6)

 

[markmzz]

So now I'm back to my original problem... so for this example with the sku colum not having any numbers. I apologize for giving the wrong data before.

qty	gp	salesperson	sku
1	4%	andrew	#N/A
1	4%	andrew	crank
2	3%	andrew	smpm
3	2%	kyle	hat3
4	4%	kyle	#N/A

<tbody>
</tbody>

My formula is this: =SUMPRODUCT(--(C2:C6="andrew"),--(D2:D6<>"#N/A"),B2:B6,A2:A6)/SUMPRODUCT(--(C2:C6="andrew"),--(D2:D6<>"#N/A"),A2:A6)

Try this:

=SUMPRODUCT(--(C2:C6="andrew"),--ISTEXT(D2:D6),B2:B6,A2:A6)/SUMPRODUCT(--(C2:C6="andrew"),--ISTEXT(D2:D6),A2:A6)

Markmzz

 

[marklemachin]

Wow that worked. I didn't know that excel doesn't consider #N/A as text.

 

[markmzz]

Wow that worked. I didn't know that excel doesn't consider #N/A as text.

You are welcome and I'm glad to help.

Markmzz