How to calculate weighted average with multiple criteria

[dahl07]

Hello,

I have figured out how to calculate the total weighted average in my data sheet with this formula:

=SUMPRODUCT((DATA!AA285:AA20000)*(DATA!N285:N20000))/(SUM(DATA!N285:N20000))

Now I need to figure out, how I can calculate this with two criterias;
1) DATA!AH285:AH20000=Distrikter!C3
2) DATA!H285:H20000=Distrikter!D2

I am not sure what to do from here - maybe IF or another SUMPRODUCT.

Thank you for considering my request, I have used several hours on this now...

 

[WinteE]

Use something like:

=SUMPRODUCT((DATA!AH285:AH20000=Distrikter!C3)*(DATA!H285:H20000Distrikter!D2)*(DATA!N285:N20000))/(SUM(DATA!N285:N20000))

 

[dahl07]

Cell returns #N/A. I have tried the formula below as well, but it still returns #N/A:

=SUMPRODUCT(--(DATA!$AH$285:$AH$34664=Distrikter!F3);DATA!$N$285:$N$34664;DATA!$AA$285:$AA$34664)/SUMIF(DATA!$AH$285:$AH$34664;Distrikter!F3;DATA!$N$285:$N$34664)

 

[dahl07]

I just noticed, that some cells in my data says #N/A - is that why? And if so, how can I exclude these in the calculation?

 

[georgestouma]

Cell returns #N/A. I have tried the formula below as well, but it still returns #N/A:

=SUMPRODUCT(--(DATA!$AH$285:$AH$34664=Distrikter!F3);DATA!$N$285:$N$34664;DATA!$AA$285:$AA$34664)/SUMIF(DATA!$AH$285:$AH$34664;Distrikter!F3;DATA!$N$285:$N$34664)

i think you must go in the cell and press CTRL SHIFT ENTER;;; i think it is better to eliminate the #N/A before you use sumproduct,, using iferror(formula, 0)

 

[dahl07]

There is a lot of #N/A, and they are looked up from another sheet as well, so I am not able to eliminate them.. How does the iferror work with my formula?

 

[Robert Mika]

I just noticed, that some cells in my data says #N/A - is that why? And if so, how can I exclude these in the calculation?

Which range include the N/A errors?
It was suggested to get rid from error before use of the SUMPRODUCT but you do not need to confirm with CTRL+SHIFT+ENTER

 

[Aladin Akyurek]

Hello,

I have figured out how to calculate the total weighted average in my data sheet with this formula:

=SUMPRODUCT((DATA!AA285:AA20000)*(DATA!N285:N20000))/(SUM(DATA!N285:N20000))

Now I need to figure out, how I can calculate this with two criterias;
1) DATA!AH285:AH20000=Distrikter!C3
2) DATA!H285:H20000=Distrikter!D2

I am not sure what to do from here - maybe IF or another SUMPRODUCT.

Thank you for considering my request, I have used several hours on this now...

Try...

=SUMPRODUCT(
    --(DATA!AH285:AH20000=Distrikter!C3),
    --(DATA!H285:H20000=Distrikter!D2),
    DATA!AA285:AA20000,
    DATA!N285:N20000)/
  SUMPRODUCT(
    --(DATA!AH285:AH20000=Distrikter!C3),
    --(DATA!H285:H20000=Distrikter!D2),
    DATA!N285:N20000)

If you re on Excel 2007 or later:

=SUMPRODUCT(
    --(DATA!AH285:AH20000=Distrikter!C3),
    --(DATA!H285:H20000=Distrikter!D2),
    DATA!AA285:AA20000,
    DATA!N285:N20000)/
  SUMIFS(
    DATA!N285:N20000
    DATA!AH285:AH20000,Distrikter!C3,
    DATA!H285:H20000,Distrikter!D2)

 

[dahl07]

Range AH includes #N/A errors

 

[dahl07]

Which range include the N/A errors?
It was suggested to get rid from error before use of the SUMPRODUCT but you do not need to confirm with CTRL+SHIFT+ENTER

Range AH includes #N/A errors