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

[marklemachin]

[TABLE="width: 165"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]qty[/TD]
[TD]gp[/TD]
[TD]salesperson[/TD]
[TD]sku[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4%[/TD]
[TD]andrew[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4%[/TD]
[TD]andrew[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3%[/TD]
[TD]andrew[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2%[/TD]
[TD]kyle[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4%[/TD]
[TD]kyle[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="width: 172"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]qty[/TD]
[TD]gp[/TD]
[TD]salesperson[/TD]
[TD]sku[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4%[/TD]
[TD]andrew[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4%[/TD]
[TD]andrew[/TD]
[TD]crank[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3%[/TD]
[TD]andrew[/TD]
[TD]smpm[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2%[/TD]
[TD]kyle[/TD]
[TD]hat3[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4%[/TD]
[TD]kyle[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 172"]
<tbody>[TR]
[TD]qty[/TD]
[TD]gp[/TD]
[TD]salesperson[/TD]
[TD]sku[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4%[/TD]
[TD]andrew[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4%[/TD]
[TD]andrew[/TD]
[TD]crank[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3%[/TD]
[TD]andrew[/TD]
[TD]smpm[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2%[/TD]
[TD]kyle[/TD]
[TD]hat3[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4%[/TD]
[TD]kyle[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

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