I'm trying to get a weighted average of columns E & H but only if the values
in column C match the values in column K. I tried nesting Sumif function
within sumproduct but this isn't working, it's just giving me a sum of colmn
H, not the weighted average. Any help would be appreciated.

=SUMPRODUCT(SUMIF($C$3:$C$320,K3,$E$3:$E$320),SUMI F($C$3:$C$320,$K3,$H$3:$H$320))/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

Hi,

My ranges are different but try something like this

=SUMPRODUCT(--(C2:C9=K2:K9),A2:A9*B2:B9)/SUMPRODUCT(B2:B9,--(C2:C9=K2:K9))

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"sj2008" wrote:

I'm trying to get a weighted average of columns E & H but only if the values
in column C match the values in column K. I tried nesting Sumif function
within sumproduct but this isn't working, it's just giving me a sum of colmn
H, not the weighted average. Any help would be appreciated.

=SUMPRODUCT(SUMIF($C$3:$C$320,K3,$E$3:$E$320),SUMI F($C$3:$C$320,$K3,$H$3:$H$320))/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

I would change the Sumif functions to SUM(IF formulaic arrays...
--
http://HelpExcel.com




"sj2008" wrote:

I'm trying to get a weighted average of columns E & H but only if the values
in column C match the values in column K. I tried nesting Sumif function
within sumproduct but this isn't working, it's just giving me a sum of colmn
H, not the weighted average. Any help would be appreciated.

=SUMPRODUCT(SUMIF($C$3:$C$320,K3,$E$3:$E$320),SUMI F($C$3:$C$320,$K3,$H$3:$H$320))/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

Control+shift+enter, not just enter...

=SUMPRODUCT(IF($C$3:$C$320=K3,$E$3:$E$320),IF($C$3 :$C$320=K3,$H$3:$H$320))/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

Is this what you intended?

sj2008 wrote:
I'm trying to get a weighted average of columns E & H but only if the values
in column C match the values in column K. I tried nesting Sumif function
within sumproduct but this isn't working, it's just giving me a sum of colmn
H, not the weighted average. Any help would be appreciated.

=SUMPRODUCT(SUMIF($C$3:$C$320,K3,$E$3:$E$320),SUMI F($C$3:$C$320,$K3,$H$3:$H$320))/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

I have tried doing this and can only get the formula to recognize the first
cell in the array. What am I doing wrong?

"galimi" wrote:

I would change the Sumif functions to SUM(IF formulaic arrays...
--
http://HelpExcel.com




"sj2008" wrote:

I'm trying to get a weighted average of columns E & H but only if the values
in column C match the values in column K. I tried nesting Sumif function
within sumproduct but this isn't working, it's just giving me a sum of colmn
H, not the weighted average. Any help would be appreciated.

=SUMPRODUCT(SUMIF($C$3:$C$320,K3,$E$3:$E$320),SUMI F($C$3:$C$320,$K3,$H$3:$H$320))/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

Enter this formula using Ctrl-Shift-Enter and not just Enter

=SUMPRODUCT(IF($C$3:$C$320=K3,$E$3:$E$320)*IF($C$3 :$C$320=$K3,$H$3:$H$320))/SUMIF($C$3:$C$320,K3,$E$3:$E$320)


HTH,
Bernie
MS Excel MVP


"DSSofMN" wrote in message
...
I have tried doing this and can only get the formula to recognize the first
cell in the array. What am I doing wrong?

"galimi" wrote:

I would change the Sumif functions to SUM(IF formulaic arrays...
--
http://HelpExcel.com




"sj2008" wrote:

I'm trying to get a weighted average of columns E & H but only if the values
in column C match the values in column K. I tried nesting Sumif function
within sumproduct but this isn't working, it's just giving me a sum of colmn
H, not the weighted average. Any help would be appreciated.

=SUMPRODUCT(SUMIF($C$3:$C$320,K3,$E$3:$E$320),SUMI F($C$3:$C$320,$K3,$H$3:$H$320))/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

Bernie - Thanks for your suggestion. The formula evaluated the first cell
which was a true condition and summed the entire column. I need the formula
to evaluate the entire column and sum the values that meet the condition.

Dan

"DSSofMN" wrote:

I have tried doing this and can only get the formula to recognize the first
cell in the array. What am I doing wrong?

"galimi" wrote:

I would change the Sumif functions to SUM(IF formulaic arrays...
--
http://HelpExcel.com




"sj2008" wrote:

I'm trying to get a weighted average of columns E & H but only if the values
in column C match the values in column K. I tried nesting Sumif function
within sumproduct but this isn't working, it's just giving me a sum of colmn
H, not the weighted average. Any help would be appreciated.

=SUMPRODUCT(SUMIF($C$3:$C$320,K3,$E$3:$E$320),SUMI F($C$3:$C$320,$K3,$H$3:$H$320))/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

Try it this way:

=SUMPRODUCT(($C$3:$C$320=K3)*$E$3:$E$320*$H$3:$H$3 20)/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

HTH,
Bernie
MS Excel MVP


"DSSofMN" wrote in message
...
Bernie - Thanks for your suggestion. The formula evaluated the first cell
which was a true condition and summed the entire column. I need the formula
to evaluate the entire column and sum the values that meet the condition.

Dan

"DSSofMN" wrote:

I have tried doing this and can only get the formula to recognize the first
cell in the array. What am I doing wrong?

"galimi" wrote:

I would change the Sumif functions to SUM(IF formulaic arrays...
--
http://HelpExcel.com




"sj2008" wrote:

I'm trying to get a weighted average of columns E & H but only if the values
in column C match the values in column K. I tried nesting Sumif function
within sumproduct but this isn't working, it's just giving me a sum of colmn
H, not the weighted average. Any help would be appreciated.

=SUMPRODUCT(SUMIF($C$3:$C$320,K3,$E$3:$E$320),SUMI F($C$3:$C$320,$K3,$H$3:$H$320))/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

I probably should have told you what I am trying to accomplish. I need to sum
a colum based on multiple criteria. The solution that you provided would work
if I was only evaluating one criteria. That's why I asked the question from
galimi's response. I was trying to do a sum(if based on multiple if
conditions.

Thanks,

Dan

"Bernie Deitrick" wrote:

Try it this way:

=SUMPRODUCT(($C$3:$C$320=K3)*$E$3:$E$320*$H$3:$H$3 20)/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

HTH,
Bernie
MS Excel MVP


"DSSofMN" wrote in message
...
Bernie - Thanks for your suggestion. The formula evaluated the first cell
which was a true condition and summed the entire column. I need the formula
to evaluate the entire column and sum the values that meet the condition.

Dan

"DSSofMN" wrote:

I have tried doing this and can only get the formula to recognize the first
cell in the array. What am I doing wrong?

"galimi" wrote:

I would change the Sumif functions to SUM(IF formulaic arrays...
--
http://HelpExcel.com




"sj2008" wrote:

I'm trying to get a weighted average of columns E & H but only if the values
in column C match the values in column K. I tried nesting Sumif function
within sumproduct but this isn't working, it's just giving me a sum of colmn
H, not the weighted average. Any help would be appreciated.

=SUMPRODUCT(SUMIF($C$3:$C$320,K3,$E$3:$E$320),SUMI F($C$3:$C$320,$K3,$H$3:$H$320))/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

For multiple criteria, use this style for AND requirements

=SUMPRODUCT(($C$3:$C$320=K3)*($D$3:$D$320=L3)*$E$3 :$E$320*$H$3:$H$320)/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

AND this for OR style

=SUMPRODUCT(($C$3:$C$320=K3)+($C$3:$C$320=K4))*$E$ 3:$E$320*$H$3:$H$320)/SUMIF($C$3:$C$320,K3,$E$3:$E$320)


HTH,

Bernie

"DSSofMN" wrote in message
...
I probably should have told you what I am trying to accomplish. I need to
sum
a colum based on multiple criteria. The solution that you provided would
work
if I was only evaluating one criteria. That's why I asked the question
from
galimi's response. I was trying to do a sum(if based on multiple if
conditions.

Thanks,

Dan

"Bernie Deitrick" wrote:

Try it this way:

=SUMPRODUCT(($C$3:$C$320=K3)*$E$3:$E$320*$H$3:$H$3 20)/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

HTH,
Bernie
MS Excel MVP


"DSSofMN" wrote in message
...
Bernie - Thanks for your suggestion. The formula evaluated the first
cell
which was a true condition and summed the entire column. I need the
formula
to evaluate the entire column and sum the values that meet the
condition.

Dan

"DSSofMN" wrote:

I have tried doing this and can only get the formula to recognize the
first
cell in the array. What am I doing wrong?

"galimi" wrote:

I would change the Sumif functions to SUM(IF formulaic arrays...
--
http://HelpExcel.com




"sj2008" wrote:

I'm trying to get a weighted average of columns E & H but only if
the values
in column C match the values in column K. I tried nesting Sumif
function
within sumproduct but this isn't working, it's just giving me a
sum of colmn
H, not the weighted average. Any help would be appreciated.

=SUMPRODUCT(SUMIF($C$3:$C$320,K3,$E$3:$E$320),SUMI F($C$3:$C$320,$K3,$H$3:$H$320))/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

Ooops, that second one needs an extra paren:

=SUMPRODUCT((($C$3:$C$320=K3)+($C$3:$C$320=K4))*$E $3:$E$320*$H$3:$H$320)/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

Bernie


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
For multiple criteria, use this style for AND requirements

=SUMPRODUCT(($C$3:$C$320=K3)*($D$3:$D$320=L3)*$E$3 :$E$320*$H$3:$H$320)/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

AND this for OR style

=SUMPRODUCT(($C$3:$C$320=K3)+($C$3:$C$320=K4))*$E$ 3:$E$320*$H$3:$H$320)/SUMIF($C$3:$C$320,K3,$E$3:$E$320)


HTH,

Bernie

"DSSofMN" wrote in message
...
I probably should have told you what I am trying to accomplish. I need to
sum
a colum based on multiple criteria. The solution that you provided would
work
if I was only evaluating one criteria. That's why I asked the question
from
galimi's response. I was trying to do a sum(if based on multiple if
conditions.

Thanks,

Dan

"Bernie Deitrick" wrote:

Try it this way:

=SUMPRODUCT(($C$3:$C$320=K3)*$E$3:$E$320*$H$3:$H$3 20)/SUMIF($C$3:$C$320,K3,$E$3:$E$320)

HTH,
Bernie
MS Excel MVP


"DSSofMN" wrote in message
...
Bernie - Thanks for your suggestion. The formula evaluated the first
cell
which was a true condition and summed the entire column. I need the
formula
to evaluate the entire column and sum the values that meet the
condition.

Dan

"DSSofMN" wrote:

I have tried doing this and can only get the formula to recognize the
first
cell in the array. What am I doing wrong?

"galimi" wrote:

I would change the Sumif functions to SUM(IF formulaic arrays...
--
http://HelpExcel.com




"sj2008" wrote:

I'm trying to get a weighted average of columns E & H but only if
the values
in column C match the values in column K. I tried nesting Sumif
function
within sumproduct but this isn't working, it's just giving me a
sum of colmn
H, not the weighted average. Any help would be appreciated.

=SUMPRODUCT(SUMIF($C$3:$C$320,K3,$E$3:$E$320),SUMI F($C$3:$C$320,$K3,$H$3:$H$320))/SUMIF($C$3:$C$320,K3,$E$3:$E$320)