Weighted Average with three criteria

[DDT~123]

Greetings all - I'm stumped on how to perform a weighted average using three criteria. I'd appreciate it if you folks could help me out. The formula if it were a simple weighted average is below, as well as the 3 criteria needed.


Criteria:
IF Sheet1!A:A="North"
IF Sheet1!B:B="Apple"
IF Sheet1!C:C="Dog"

Simple Formula if criteria wasn't needed:
=SUMPRODUCT(Sheet1!K:K,Sheet1!H:H)/SUM(Sheet1!H:H)

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(--(Sheet1!A:A="North"),--(Sheet1!B:B="Apple"),--(Sheet1!C:C="Dog"),Sheet1!K:K,Sheet1!H:H)/
SUMIFS(Sheet1!H:H,Sheet1!A:A,"North",Sheet1!B:B,"Apple",Sheet1!C:C,"Dog")

 

[DDT~123]

Thank you for the quick response. Unfortunately that produced a #DIV/0! error.

 

[Aladin Akyurek]

Thank you for the quick response. Unfortunately that produced a #DIV/0! error.

Justified or unjustified?

 

[DDT~123]

I believe unjustified

 

[Aladin Akyurek]

I believe unjustified

Let's check the denominator...

Is this greater than zero?

=SUM(Sheet1!H:H)

And is this greater than zero?

=SUMIFS(Sheet1!H:H,Sheet1!A:A,"North",Sheet1!B:B,"Apple",Sheet1!C:C,"Dog")

If this returns zero, test the following:

=COUNTIFS(Sheet1!A:A,"North",Sheet1!B:B,"Apple",Sheet1!C:C,"Dog")

to see whether this returns a zero count.​

​

<strike></strike><strike></strike>

 

[DDT~123]

Let's check the denominator...

Is this greater than zero?

=SUM(Sheet1!H:H)

And is this greater than zero?

=SUMIFS(Sheet1!H:H,Sheet1!A:A,"North",Sheet1!B:B,"Apple",Sheet1!C:C,"Dog")

If this returns zero, test the following:

=COUNTIFS(Sheet1!A:A,"North",Sheet1!B:B,"Apple",Sheet1!C:C,"Dog")

to see whether this returns a zero count.​

​

<strike></strike><strike></strike>

=SUM(Sheet1!H:H) returned 12,162
=SUMIFS(Sheet1!H:H,Sheet1!A:A,"North",Sheet1!B:B,"Apple",Sheet1!C:C,"Dog") returned 329

 

[DDT~123]

I just figured it out Had to do a little bit of scrubbing due to some of the columns which were referenced in the weighted average included alpha characters. I added a helper column with an IF statement to change the alpha characters to blank. Thank you again for your help

 

[Aladin Akyurek]

I just figured it out Had to do a little bit of scrubbing due to some of the columns which were referenced in the weighted average included alpha characters. I added a helper column with an IF statement to change the alpha characters to blank. Thank you again for your help

You are welcome. Thanks for the update.