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

 

[steve the fish]

Try using NOT(ISERROR(D2:D6))

 

[marklemachin]

In what part of the formula?

 

[Marcelo Branco]

Maybe...

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

M.

 

[marklemachin]

Wow that worked. Thanks for this! I've been trying soooo many different ways.

 

[marklemachin]

Well, now that I plugged that equation into my real sheet i'm getting a #div/0! error.

 

[steve the fish]

Lets see the formula

 

[marklemachin]

=SUMPRODUCT(--(vlookup!A:A="Andrew Cicisly"),--ISNUMBER(vlookup!C:C),Sales!F:F,Sales!D:D)/SUMPRODUCT(--(vlookup!A:A="Andrew Cicisly"),--ISNUMBER(vlookup!C:C),Sales!D:D)

Where column F is Gross Profit and column D is Qty. The only difference between the example equation that didn't give me an error and this actual equation is that I'm using the whole column instead of just a range. P.S...the columns on both tabs are same length.

 

[steve the fish]

The reason you are getting divided by zero error is that SUMPRODUCT(--(vlookup!A:A="Andrew Cicisly"),--ISNUMBER(vlookup!C:C),Sales!D:D) equals zero. That said what are you doing there? Why are you using two different sheets? Ive used countless sumproduct formulas and have never used one that references different sheets in that manner. Are you saying your data is in different sheets but that the rows are connected somehow?

 

[marklemachin]

Yes, its a very large workbook with many different vlookups that tell me whether or not a sale of a sku is in a specific sku category. I was able to find the sum of averages for Gross Profit but my boss wants a weighted average instead. This formula below worked for the sum of averages but now I'm having trouble with weighted average.

=IFERROR(AVERAGEIFS(Sales!F:F,vlookup!$A:$A,"Andrew Cicisly",vlookup!$C:$C,"<>"&"#N/A",Sales!F:F,"<>0"),0)