sumif - multiple conditions

sumproduct will work for what you want
=sumproduct(--(monthrange=month),--(yearrange=year),datarange)
note the --) makes the logical true-false become a numeric 1-0
also in Sumproduct all of the arrays have ot be the same size and cannot be
the shorthand column Description.
A:A wont work, A1:A65000 would
"J_Barn" wrote:

> I need to sum values by month/year (Ex: Jan 2006) based on data that I've set
> up in an Excel spreadsheet.
>
> My spreadsheet is set up in this fashion.
> StartMonth : StartYear: Month1 : Month2 : Month3 : ...Month24
> 12 : 2005: 400 : 250 : 1000 : etc
> 1 : 2006 : 300 : 650 : 2500 : etc
>
> Based on the Start Month and Start Year values, I want to sum the
> appropriate figures into a Grand total for that particular month.
>
> In this instance, Jan 2006 should = 550, Feb 2006=1650, Mar 2006=2500
>
> Does anyone have a solution to this? I'm in desperate need of some help.
> Thank you.
>
>

I'm not sure that this alone will work for what I'm trying to do.

My table has start month and start year and then the rest of the columns are
values of data (projected sales) out for the next 24 months.

So if on the first row, the start month and start year= Jan 2004 - the first
cell value under column "1" would correspond to Jan 2004, column "2" would be
Feb 2004 and so on. The next row may have Mar 2004 as the start month/year
and therefore the column "1" would correspond to Mar 2004, the 2nd would be
Apr 2004.

My calculation needs to sum all cells that would equivlate to the particular
month and year in the spreadsheet. It needs to "understand" that Jan 2004 is
a summation of an array of different cells (ex:C10,D32,E15,...J80) depending
on the original start month and year + the 1-24 months out I forecasted
values for.

This may be a little confusing... let me know if I need to provide more info
for it to make sense.



"bj" wrote:

> sumproduct will work for what you want
> =sumproduct(--(monthrange=month),--(yearrange=year),datarange)
> note the --) makes the logical true-false become a numeric 1-0
> also in Sumproduct all of the arrays have ot be the same size and cannot be
> the shorthand column Description.
> A:A wont work, A1:A65000 would
> "J_Barn" wrote:
>
> > I need to sum values by month/year (Ex: Jan 2006) based on data that I've set
> > up in an Excel spreadsheet.
> >
> > My spreadsheet is set up in this fashion.
> > StartMonth : StartYear: Month1 : Month2 : Month3 : ...Month24
> > 12 : 2005: 400 : 250 : 1000 : etc
> > 1 : 2006 : 300 : 650 : 2500 : etc
> >
> > Based on the Start Month and Start Year values, I want to sum the
> > appropriate figures into a Grand total for that particular month.
> >
> > In this instance, Jan 2006 should = 550, Feb 2006=1650, Mar 2006=2500
> >
> > Does anyone have a solution to this? I'm in desperate need of some help.
> > Thank you.
> >
> >

You are right it is confusing.
I appoligize for not not understanding that it is multiple cells you need to
sum
if the cells are from as varied a source as is listed in your example. I
suggest you have a helper column which does all of the sums needed for any
given date and reference that column .
if they were all in one row you could do the same or change the equation to
=sum(offset(A1,sumproduct(--(monthrange=month),--(yearrange=year),row(monthrange)),3,1,24)

"J_Barn" wrote:

> I'm not sure that this alone will work for what I'm trying to do.
>
> My table has start month and start year and then the rest of the columns are
> values of data (projected sales) out for the next 24 months.
>
> So if on the first row, the start month and start year= Jan 2004 - the first
> cell value under column "1" would correspond to Jan 2004, column "2" would be
> Feb 2004 and so on. The next row may have Mar 2004 as the start month/year
> and therefore the column "1" would correspond to Mar 2004, the 2nd would be
> Apr 2004.
>
> My calculation needs to sum all cells that would equivlate to the particular
> month and year in the spreadsheet. It needs to "understand" that Jan 2004 is
> a summation of an array of different cells (ex:C10,D32,E15,...J80) depending
> on the original start month and year + the 1-24 months out I forecasted
> values for.
>
> This may be a little confusing... let me know if I need to provide more info
> for it to make sense.
>
>
>
> "bj" wrote:
>
> > sumproduct will work for what you want
> > =sumproduct(--(monthrange=month),--(yearrange=year),datarange)
> > note the --) makes the logical true-false become a numeric 1-0
> > also in Sumproduct all of the arrays have ot be the same size and cannot be
> > the shorthand column Description.
> > A:A wont work, A1:A65000 would
> > "J_Barn" wrote:
> >
> > > I need to sum values by month/year (Ex: Jan 2006) based on data that I've set
> > > up in an Excel spreadsheet.
> > >
> > > My spreadsheet is set up in this fashion.
> > > StartMonth : StartYear: Month1 : Month2 : Month3 : ...Month24
> > > 12 : 2005: 400 : 250 : 1000 : etc
> > > 1 : 2006 : 300 : 650 : 2500 : etc
> > >
> > > Based on the Start Month and Start Year values, I want to sum the
> > > appropriate figures into a Grand total for that particular month.
> > >
> > > In this instance, Jan 2006 should = 550, Feb 2006=1650, Mar 2006=2500
> > >
> > > Does anyone have a solution to this? I'm in desperate need of some help.
> > > Thank you.
> > >
> > >

For this example, let's assume the following...

1) Three month's worth of data

1) A1:E3 contains the data

2) First row contains your headers/labels

3) C2:E3 contains your monthly values to sum

4) A10 contains the month number of interest, such as 1

5) B10 contains the year of interest, such as 2006

Try the following formula...

=SUMPRODUCT(SUBTOTAL(9,OFFSET($C$2:$E$3,ROW($C$2:$E$3)-MIN(ROW($C$2:$E$3)
),DATEDIF(DATE($B$2:$B$3,$A$2:$A$3,1),DATE(B10,A10,1),"M"),1,1)))

....which requires that you enable the Analysis ToolPak add-in...

Tools > Add-In > and check Analysis ToolPak

Hope this helps!

In article <[email protected]>,
J_Barn <[email protected]> wrote:

> I need to sum values by month/year (Ex: Jan 2006) based on data that I've set
> up in an Excel spreadsheet.
>
> My spreadsheet is set up in this fashion.
> StartMonth : StartYear: Month1 : Month2 : Month3 : ...Month24
> 12 : 2005: 400 : 250 : 1000 : etc
> 1 : 2006 : 300 : 650 : 2500 : etc
>
> Based on the Start Month and Start Year values, I want to sum the
> appropriate figures into a Grand total for that particular month.
>
> In this instance, Jan 2006 should = 550, Feb 2006=1650, Mar 2006=2500
>
> Does anyone have a solution to this? I'm in desperate need of some help.
> Thank you.

sumproduct will work for what you want
=sumproduct(--(monthrange=month),--(yearrange=year),datarange)
note the --) makes the logical true-false become a numeric 1-0
also in Sumproduct all of the arrays have ot be the same size and cannot be
the shorthand column Description.
A:A wont work, A1:A65000 would
"J_Barn" wrote:

> I need to sum values by month/year (Ex: Jan 2006) based on data that I've set
> up in an Excel spreadsheet.
>
> My spreadsheet is set up in this fashion.
> StartMonth : StartYear: Month1 : Month2 : Month3 : ...Month24
> 12 : 2005: 400 : 250 : 1000 : etc
> 1 : 2006 : 300 : 650 : 2500 : etc
>
> Based on the Start Month and Start Year values, I want to sum the
> appropriate figures into a Grand total for that particular month.
>
> In this instance, Jan 2006 should = 550, Feb 2006=1650, Mar 2006=2500
>
> Does anyone have a solution to this? I'm in desperate need of some help.
> Thank you.
>
>

I'm not sure that this alone will work for what I'm trying to do.

My table has start month and start year and then the rest of the columns are
values of data (projected sales) out for the next 24 months.

So if on the first row, the start month and start year= Jan 2004 - the first
cell value under column "1" would correspond to Jan 2004, column "2" would be
Feb 2004 and so on. The next row may have Mar 2004 as the start month/year
and therefore the column "1" would correspond to Mar 2004, the 2nd would be
Apr 2004.

My calculation needs to sum all cells that would equivlate to the particular
month and year in the spreadsheet. It needs to "understand" that Jan 2004 is
a summation of an array of different cells (ex:C10,D32,E15,...J80) depending
on the original start month and year + the 1-24 months out I forecasted
values for.

This may be a little confusing... let me know if I need to provide more info
for it to make sense.



"bj" wrote:

> sumproduct will work for what you want
> =sumproduct(--(monthrange=month),--(yearrange=year),datarange)
> note the --) makes the logical true-false become a numeric 1-0
> also in Sumproduct all of the arrays have ot be the same size and cannot be
> the shorthand column Description.
> A:A wont work, A1:A65000 would
> "J_Barn" wrote:
>
> > I need to sum values by month/year (Ex: Jan 2006) based on data that I've set
> > up in an Excel spreadsheet.
> >
> > My spreadsheet is set up in this fashion.
> > StartMonth : StartYear: Month1 : Month2 : Month3 : ...Month24
> > 12 : 2005: 400 : 250 : 1000 : etc
> > 1 : 2006 : 300 : 650 : 2500 : etc
> >
> > Based on the Start Month and Start Year values, I want to sum the
> > appropriate figures into a Grand total for that particular month.
> >
> > In this instance, Jan 2006 should = 550, Feb 2006=1650, Mar 2006=2500
> >
> > Does anyone have a solution to this? I'm in desperate need of some help.
> > Thank you.
> >
> >

You are right it is confusing.
I appoligize for not not understanding that it is multiple cells you need to
sum
if the cells are from as varied a source as is listed in your example. I
suggest you have a helper column which does all of the sums needed for any
given date and reference that column .
if they were all in one row you could do the same or change the equation to
=sum(offset(A1,sumproduct(--(monthrange=month),--(yearrange=year),row(monthrange)),3,1,24)

"J_Barn" wrote:

> I'm not sure that this alone will work for what I'm trying to do.
>
> My table has start month and start year and then the rest of the columns are
> values of data (projected sales) out for the next 24 months.
>
> So if on the first row, the start month and start year= Jan 2004 - the first
> cell value under column "1" would correspond to Jan 2004, column "2" would be
> Feb 2004 and so on. The next row may have Mar 2004 as the start month/year
> and therefore the column "1" would correspond to Mar 2004, the 2nd would be
> Apr 2004.
>
> My calculation needs to sum all cells that would equivlate to the particular
> month and year in the spreadsheet. It needs to "understand" that Jan 2004 is
> a summation of an array of different cells (ex:C10,D32,E15,...J80) depending
> on the original start month and year + the 1-24 months out I forecasted
> values for.
>
> This may be a little confusing... let me know if I need to provide more info
> for it to make sense.
>
>
>
> "bj" wrote:
>
> > sumproduct will work for what you want
> > =sumproduct(--(monthrange=month),--(yearrange=year),datarange)
> > note the --) makes the logical true-false become a numeric 1-0
> > also in Sumproduct all of the arrays have ot be the same size and cannot be
> > the shorthand column Description.
> > A:A wont work, A1:A65000 would
> > "J_Barn" wrote:
> >
> > > I need to sum values by month/year (Ex: Jan 2006) based on data that I've set
> > > up in an Excel spreadsheet.
> > >
> > > My spreadsheet is set up in this fashion.
> > > StartMonth : StartYear: Month1 : Month2 : Month3 : ...Month24
> > > 12 : 2005: 400 : 250 : 1000 : etc
> > > 1 : 2006 : 300 : 650 : 2500 : etc
> > >
> > > Based on the Start Month and Start Year values, I want to sum the
> > > appropriate figures into a Grand total for that particular month.
> > >
> > > In this instance, Jan 2006 should = 550, Feb 2006=1650, Mar 2006=2500
> > >
> > > Does anyone have a solution to this? I'm in desperate need of some help.
> > > Thank you.
> > >
> > >

For this example, let's assume the following...

1) Three month's worth of data

1) A1:E3 contains the data

2) First row contains your headers/labels

3) C2:E3 contains your monthly values to sum

4) A10 contains the month number of interest, such as 1

5) B10 contains the year of interest, such as 2006

Try the following formula...

=SUMPRODUCT(SUBTOTAL(9,OFFSET($C$2:$E$3,ROW($C$2:$E$3)-MIN(ROW($C$2:$E$3)
),DATEDIF(DATE($B$2:$B$3,$A$2:$A$3,1),DATE(B10,A10,1),"M"),1,1)))

....which requires that you enable the Analysis ToolPak add-in...

Tools > Add-In > and check Analysis ToolPak

Hope this helps!

In article <[email protected]>,
J_Barn <[email protected]> wrote:

> I need to sum values by month/year (Ex: Jan 2006) based on data that I've set
> up in an Excel spreadsheet.
>
> My spreadsheet is set up in this fashion.
> StartMonth : StartYear: Month1 : Month2 : Month3 : ...Month24
> 12 : 2005: 400 : 250 : 1000 : etc
> 1 : 2006 : 300 : 650 : 2500 : etc
>
> Based on the Start Month and Start Year values, I want to sum the
> appropriate figures into a Grand total for that particular month.
>
> In this instance, Jan 2006 should = 550, Feb 2006=1650, Mar 2006=2500
>
> Does anyone have a solution to this? I'm in desperate need of some help.
> Thank you.

I need to sum values by month/year (Ex: Jan 2006) based on data that I've set
up in an Excel spreadsheet.

My spreadsheet is set up in this fashion.
StartMonth : StartYear: Month1 : Month2 : Month3 : ...Month24
12 : 2005: 400 : 250 : 1000 : etc
1 : 2006 : 300 : 650 : 2500 : etc

Based on the Start Month and Start Year values, I want to sum the
appropriate figures into a Grand total for that particular month.

In this instance, Jan 2006 should = 550, Feb 2006=1650, Mar 2006=2500

Does anyone have a solution to this? I'm in desperate need of some help.
Thank you.

sumproduct will work for what you want
=sumproduct(--(monthrange=month),--(yearrange=year),datarange)
note the --) makes the logical true-false become a numeric 1-0
also in Sumproduct all of the arrays have ot be the same size and cannot be
the shorthand column Description.
A:A wont work, A1:A65000 would
"J_Barn" wrote:

> I need to sum values by month/year (Ex: Jan 2006) based on data that I've set
> up in an Excel spreadsheet.
>
> My spreadsheet is set up in this fashion.
> StartMonth : StartYear: Month1 : Month2 : Month3 : ...Month24
> 12 : 2005: 400 : 250 : 1000 : etc
> 1 : 2006 : 300 : 650 : 2500 : etc
>
> Based on the Start Month and Start Year values, I want to sum the
> appropriate figures into a Grand total for that particular month.
>
> In this instance, Jan 2006 should = 550, Feb 2006=1650, Mar 2006=2500
>
> Does anyone have a solution to this? I'm in desperate need of some help.
> Thank you.
>
>

I'm not sure that this alone will work for what I'm trying to do.

My table has start month and start year and then the rest of the columns are
values of data (projected sales) out for the next 24 months.

So if on the first row, the start month and start year= Jan 2004 - the first
cell value under column "1" would correspond to Jan 2004, column "2" would be
Feb 2004 and so on. The next row may have Mar 2004 as the start month/year
and therefore the column "1" would correspond to Mar 2004, the 2nd would be
Apr 2004.

My calculation needs to sum all cells that would equivlate to the particular
month and year in the spreadsheet. It needs to "understand" that Jan 2004 is
a summation of an array of different cells (ex:C10,D32,E15,...J80) depending
on the original start month and year + the 1-24 months out I forecasted
values for.

This may be a little confusing... let me know if I need to provide more info
for it to make sense.



"bj" wrote:

> sumproduct will work for what you want
> =sumproduct(--(monthrange=month),--(yearrange=year),datarange)
> note the --) makes the logical true-false become a numeric 1-0
> also in Sumproduct all of the arrays have ot be the same size and cannot be
> the shorthand column Description.
> A:A wont work, A1:A65000 would
> "J_Barn" wrote:
>
> > I need to sum values by month/year (Ex: Jan 2006) based on data that I've set
> > up in an Excel spreadsheet.
> >
> > My spreadsheet is set up in this fashion.
> > StartMonth : StartYear: Month1 : Month2 : Month3 : ...Month24
> > 12 : 2005: 400 : 250 : 1000 : etc
> > 1 : 2006 : 300 : 650 : 2500 : etc
> >
> > Based on the Start Month and Start Year values, I want to sum the
> > appropriate figures into a Grand total for that particular month.
> >
> > In this instance, Jan 2006 should = 550, Feb 2006=1650, Mar 2006=2500
> >
> > Does anyone have a solution to this? I'm in desperate need of some help.
> > Thank you.
> >
> >

You are right it is confusing.
I appoligize for not not understanding that it is multiple cells you need to
sum
if the cells are from as varied a source as is listed in your example. I
suggest you have a helper column which does all of the sums needed for any
given date and reference that column .
if they were all in one row you could do the same or change the equation to
=sum(offset(A1,sumproduct(--(monthrange=month),--(yearrange=year),row(monthrange)),3,1,24)

"J_Barn" wrote:

> I'm not sure that this alone will work for what I'm trying to do.
>
> My table has start month and start year and then the rest of the columns are
> values of data (projected sales) out for the next 24 months.
>
> So if on the first row, the start month and start year= Jan 2004 - the first
> cell value under column "1" would correspond to Jan 2004, column "2" would be
> Feb 2004 and so on. The next row may have Mar 2004 as the start month/year
> and therefore the column "1" would correspond to Mar 2004, the 2nd would be
> Apr 2004.
>
> My calculation needs to sum all cells that would equivlate to the particular
> month and year in the spreadsheet. It needs to "understand" that Jan 2004 is
> a summation of an array of different cells (ex:C10,D32,E15,...J80) depending
> on the original start month and year + the 1-24 months out I forecasted
> values for.
>
> This may be a little confusing... let me know if I need to provide more info
> for it to make sense.
>
>
>
> "bj" wrote:
>
> > sumproduct will work for what you want
> > =sumproduct(--(monthrange=month),--(yearrange=year),datarange)
> > note the --) makes the logical true-false become a numeric 1-0
> > also in Sumproduct all of the arrays have ot be the same size and cannot be
> > the shorthand column Description.
> > A:A wont work, A1:A65000 would
> > "J_Barn" wrote:
> >
> > > I need to sum values by month/year (Ex: Jan 2006) based on data that I've set
> > > up in an Excel spreadsheet.
> > >
> > > My spreadsheet is set up in this fashion.
> > > StartMonth : StartYear: Month1 : Month2 : Month3 : ...Month24
> > > 12 : 2005: 400 : 250 : 1000 : etc
> > > 1 : 2006 : 300 : 650 : 2500 : etc
> > >
> > > Based on the Start Month and Start Year values, I want to sum the
> > > appropriate figures into a Grand total for that particular month.
> > >
> > > In this instance, Jan 2006 should = 550, Feb 2006=1650, Mar 2006=2500
> > >
> > > Does anyone have a solution to this? I'm in desperate need of some help.
> > > Thank you.
> > >
> > >

For this example, let's assume the following...

1) Three month's worth of data

1) A1:E3 contains the data

2) First row contains your headers/labels

3) C2:E3 contains your monthly values to sum

4) A10 contains the month number of interest, such as 1

5) B10 contains the year of interest, such as 2006

Try the following formula...

=SUMPRODUCT(SUBTOTAL(9,OFFSET($C$2:$E$3,ROW($C$2:$E$3)-MIN(ROW($C$2:$E$3)
),DATEDIF(DATE($B$2:$B$3,$A$2:$A$3,1),DATE(B10,A10,1),"M"),1,1)))

....which requires that you enable the Analysis ToolPak add-in...

Tools > Add-In > and check Analysis ToolPak

Hope this helps!

In article <[email protected]>,
J_Barn <[email protected]> wrote:

> I need to sum values by month/year (Ex: Jan 2006) based on data that I've set
> up in an Excel spreadsheet.
>
> My spreadsheet is set up in this fashion.
> StartMonth : StartYear: Month1 : Month2 : Month3 : ...Month24
> 12 : 2005: 400 : 250 : 1000 : etc
> 1 : 2006 : 300 : 650 : 2500 : etc
>
> Based on the Start Month and Start Year values, I want to sum the
> appropriate figures into a Grand total for that particular month.
>
> In this instance, Jan 2006 should = 550, Feb 2006=1650, Mar 2006=2500
>
> Does anyone have a solution to this? I'm in desperate need of some help.
> Thank you.