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.

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.

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.

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?

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.

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?

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.