|
Nesting SUMIF within SUMPRODUCT
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) |
Nesting SUMIF within SUMPRODUCT
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) |
Nesting SUMIF within SUMPRODUCT
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) |
Nesting SUMIF within SUMPRODUCT
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) |
Nesting SUMIF within SUMPRODUCT
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) |
Nesting SUMIF within SUMPRODUCT
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) |
Nesting SUMIF within SUMPRODUCT
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) |
Nesting SUMIF within SUMPRODUCT
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) |
Nesting SUMIF within SUMPRODUCT
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) |
Nesting SUMIF within SUMPRODUCT
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) |
| All times are GMT +1. The time now is 06:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com