Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with nested sum
Here's my sumproduct formula:
=SUMPRODUCT(--(Data!$F$2:$F$2500='BDC P&L'!$C$607)*(Data!$D$2:$D$2500='BDC P&L'!C608)*(Data!$A$2:$A$2500='BDC P&L'!$C$605)*(Data!$C$2:$C$2500='BDC P&L'!$B612)*(Data!$E$2:$E$2500)) This formula is working fine as it is. But one of my criteria (Col D) is listing data for the entire year (13 periods). So I needed to match the criteria to the current + past periods only. For example, If I'm on P04, I will need to return data for the first 3 periods only. If I omit the criteria, the formula will sum up all the data for all 13 periods, and if I leave it as it is, it will return only the values that match the data under 'BDC P&L'!C608. I have tried something like this but didn't work: ........)*(Data!$D$2:$D$2500='BDC P&L'!C608 + 'BDC P&L'!D608 + 'BDC P&L'!E608 )*(..........)) I also nested sum into that one criteria, but i didn't work either. Columns A, C, D, and F are text type, while column E is numerical type. Any direction on this is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with nested sum
I have managed to use an array within that criteria,and get it to work, but
it seems like a manual process, meaning every period I have to change my formulas to include the new period, and this is going to be tedious as my workbook is quite long with many formulas and projects. Here's how I got it to work manually: ........)*(Data!$D$2:$D$2500= {"01","02","03"})*(........... Is there a way to have this work automatically? Thanks. -- when u change the way u look @ things, the things u look at change. "sahafi" wrote: Here's my sumproduct formula: =SUMPRODUCT(--(Data!$F$2:$F$2500='BDC P&L'!$C$607)*(Data!$D$2:$D$2500='BDC P&L'!C608)*(Data!$A$2:$A$2500='BDC P&L'!$C$605)*(Data!$C$2:$C$2500='BDC P&L'!$B612)*(Data!$E$2:$E$2500)) This formula is working fine as it is. But one of my criteria (Col D) is listing data for the entire year (13 periods). So I needed to match the criteria to the current + past periods only. For example, If I'm on P04, I will need to return data for the first 3 periods only. If I omit the criteria, the formula will sum up all the data for all 13 periods, and if I leave it as it is, it will return only the values that match the data under 'BDC P&L'!C608. I have tried something like this but didn't work: .......)*(Data!$D$2:$D$2500='BDC P&L'!C608 + 'BDC P&L'!D608 + 'BDC P&L'!E608 )*(..........)) I also nested sum into that one criteria, but i didn't work either. Columns A, C, D, and F are text type, while column E is numerical type. Any direction on this is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with nested sum
Can you not do:
........)*(Data!$D$2:$D$2500<= X1)*(........... where X1=last period required e.g "03" "sahafi" wrote: I have managed to use an array within that criteria,and get it to work, but it seems like a manual process, meaning every period I have to change my formulas to include the new period, and this is going to be tedious as my workbook is quite long with many formulas and projects. Here's how I got it to work manually: .......)*(Data!$D$2:$D$2500= {"01","02","03"})*(........... Is there a way to have this work automatically? Thanks. -- when u change the way u look @ things, the things u look at change. "sahafi" wrote: Here's my sumproduct formula: =SUMPRODUCT(--(Data!$F$2:$F$2500='BDC P&L'!$C$607)*(Data!$D$2:$D$2500='BDC P&L'!C608)*(Data!$A$2:$A$2500='BDC P&L'!$C$605)*(Data!$C$2:$C$2500='BDC P&L'!$B612)*(Data!$E$2:$E$2500)) This formula is working fine as it is. But one of my criteria (Col D) is listing data for the entire year (13 periods). So I needed to match the criteria to the current + past periods only. For example, If I'm on P04, I will need to return data for the first 3 periods only. If I omit the criteria, the formula will sum up all the data for all 13 periods, and if I leave it as it is, it will return only the values that match the data under 'BDC P&L'!C608. I have tried something like this but didn't work: .......)*(Data!$D$2:$D$2500='BDC P&L'!C608 + 'BDC P&L'!D608 + 'BDC P&L'!E608 )*(..........)) I also nested sum into that one criteria, but i didn't work either. Columns A, C, D, and F are text type, while column E is numerical type. Any direction on this is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with nested sum
Another thought, think you could try using a defined range,
eg define D as: D: ={"01","02","03"} Then apply it as: ........)*(Data!$D$2:$D$2500=D)*(........... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "sahafi" wrote: I have managed to use an array within that criteria,and get it to work, but it seems like a manual process, meaning every period I have to change my formulas to include the new period, and this is going to be tedious as my workbook is quite long with many formulas and projects. Here's how I got it to work manually: .......)*(Data!$D$2:$D$2500= {"01","02","03"})*(........... Is there a way to have this work automatically? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with nested sum
Thanks Toppers. That did the trick.
-- when u change the way u look @ things, the things u look at change. "Toppers" wrote: Can you not do: .......)*(Data!$D$2:$D$2500<= X1)*(........... where X1=last period required e.g "03" "sahafi" wrote: I have managed to use an array within that criteria,and get it to work, but it seems like a manual process, meaning every period I have to change my formulas to include the new period, and this is going to be tedious as my workbook is quite long with many formulas and projects. Here's how I got it to work manually: .......)*(Data!$D$2:$D$2500= {"01","02","03"})*(........... Is there a way to have this work automatically? Thanks. -- when u change the way u look @ things, the things u look at change. "sahafi" wrote: Here's my sumproduct formula: =SUMPRODUCT(--(Data!$F$2:$F$2500='BDC P&L'!$C$607)*(Data!$D$2:$D$2500='BDC P&L'!C608)*(Data!$A$2:$A$2500='BDC P&L'!$C$605)*(Data!$C$2:$C$2500='BDC P&L'!$B612)*(Data!$E$2:$E$2500)) This formula is working fine as it is. But one of my criteria (Col D) is listing data for the entire year (13 periods). So I needed to match the criteria to the current + past periods only. For example, If I'm on P04, I will need to return data for the first 3 periods only. If I omit the criteria, the formula will sum up all the data for all 13 periods, and if I leave it as it is, it will return only the values that match the data under 'BDC P&L'!C608. I have tried something like this but didn't work: .......)*(Data!$D$2:$D$2500='BDC P&L'!C608 + 'BDC P&L'!D608 + 'BDC P&L'!E608 )*(..........)) I also nested sum into that one criteria, but i didn't work either. Columns A, C, D, and F are text type, while column E is numerical type. Any direction on this is greatly appreciated. Thanks. -- when u change the way u look @ things, the things u look at change. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with nested sum
Thanks Max for the reply, but i'm not sure how would that automate the
process. Is that mean I have to change the range each period? Because the data comes in for the entire year (13 period), and I only needed to sum what I need according to my current period. Toppers example will do the job for now. Thanks. -- when u change the way u look @ things, the things u look at change. "Max" wrote: Another thought, think you could try using a defined range, eg define D as: D: ={"01","02","03"} Then apply it as: .......)*(Data!$D$2:$D$2500=D)*(........... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "sahafi" wrote: I have managed to use an array within that criteria,and get it to work, but it seems like a manual process, meaning every period I have to change my formulas to include the new period, and this is going to be tedious as my workbook is quite long with many formulas and projects. Here's how I got it to work manually: .......)*(Data!$D$2:$D$2500= {"01","02","03"})*(........... Is there a way to have this work automatically? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with nested sum
Glad to hear you got it from Topper's suggestion. Pl dismiss the earlier
thought. It wasn't quite relevant here. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "sahafi" wrote in message ... Thanks Max for the reply, but i'm not sure how would that automate the process. Is that mean I have to change the range each period? Because the data comes in for the entire year (13 period), and I only needed to sum what I need according to my current period. Toppers example will do the job for now. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
nested if based on nested if in seperate sheet. how? | Excel Worksheet Functions | |||
Nested If help | Excel Worksheet Functions | |||
Nested IF | Excel Discussion (Misc queries) | |||
Sumproduct (Sumif) with Nested Or Criteria | Excel Worksheet Functions | |||
Nested sumif/sumproduct | Excel Worksheet Functions |