Sum of named ranges conditional to date?

Hi,

I have a similar situation, but with data in rows.
Here is the formula which does not work for me (why?) but when I change
today's date to a number it works.

=SUMIF($AA$3:$CU$3,$X$1,$AA4:$CU4)

top row AA3 - CU3 contains months.
below row AA4 - CU4 contains values
my objective is to sum all of the values for past months ( in comparison to
current month, which is located in the cell X1)

Thanks, Gus




"SleazyBreezy" wrote:

Hi all,

Say I have a spreadsheet listed as follows:

A1 = 03/31/05
A2 = $5.00

B1 = 03/31/05
B2 = $10.00

C1 = 04/01/05
C2 = $20.00

The entire A column is named "Base_Date" and the entire B column is named
"Base_Amount".

I would like a formula that will sum values in the Amount column that
correspond with a specific date range, ie. March only. This formula must
reside in a different worksheet (within the same workbook).

I've tried the following:

=SUM(IF(Base_Date=DATEVALUE("03/01/2005"),IF(Base_Date=DATEVALUE("03/31/2005"),Base_Amount,0),0))

If I place the formula in a seperate worksheet from the data (as intended),
its value is $35.00 (adds all values in column B) when I'm expecting it to be
only $15.00.

If I place the formula in the same worksheet as the data (not in columns A
or B), its value is reported as 0.

I'm sure I'm missing something miniscule here. Any suggestions?

TIA,
Sleazy

top row AA3 - CU3 contains months.

In what form? Are they the month names as *TEXT* entrires: Jan, Feb, Mar?

Are they *DATES* formatted to display the month name?

when I change today's date to a number it works.

How are you changing today's date to a number? What number?

Maybe you want something like this:

=SUMPRODUCT(--(MONTH($AA$3:$CU$3)MONTH($X$1)),$AA4:$CU4)


--
Biff
Microsoft Excel MVP


"Gus" wrote in message
...
Hi,

I have a similar situation, but with data in rows.
Here is the formula which does not work for me (why?) but when I change
today's date to a number it works.

=SUMIF($AA$3:$CU$3,$X$1,$AA4:$CU4)

top row AA3 - CU3 contains months.
below row AA4 - CU4 contains values
my objective is to sum all of the values for past months ( in comparison
to
current month, which is located in the cell X1)

Thanks, Gus




"SleazyBreezy" wrote:

Hi all,

Say I have a spreadsheet listed as follows:

A1 = 03/31/05
A2 = $5.00

B1 = 03/31/05
B2 = $10.00

C1 = 04/01/05
C2 = $20.00

The entire A column is named "Base_Date" and the entire B column is named
"Base_Amount".

I would like a formula that will sum values in the Amount column that
correspond with a specific date range, ie. March only. This formula must
reside in a different worksheet (within the same workbook).

I've tried the following:

=SUM(IF(Base_Date=DATEVALUE("03/01/2005"),IF(Base_Date=DATEVALUE("03/31/2005"),Base_Amount,0),0))

If I place the formula in a seperate worksheet from the data (as
intended),
its value is $35.00 (adds all values in column B) when I'm expecting it
to be
only $15.00.

If I place the formula in the same worksheet as the data (not in columns
A
or B), its value is reported as 0.

I'm sure I'm missing something miniscule here. Any suggestions?

TIA,
Sleazy

The top raw is in the date format ( 1/1/2008 to display Jan-08).
If the date in cell X1 is formated same way as above, the formula does not
work, why?.
Once I change it to =39814 then the formula works.

Gus





"T. Valko" wrote:

top row AA3 - CU3 contains months.

In what form? Are they the month names as *TEXT* entrires: Jan, Feb, Mar?

Are they *DATES* formatted to display the month name?

when I change today's date to a number it works.

How are you changing today's date to a number? What number?

Maybe you want something like this:

=SUMPRODUCT(--(MONTH($AA$3:$CU$3)MONTH($X$1)),$AA4:$CU4)


--
Biff
Microsoft Excel MVP


"Gus" wrote in message
...
Hi,

I have a similar situation, but with data in rows.
Here is the formula which does not work for me (why?) but when I change
today's date to a number it works.

=SUMIF($AA$3:$CU$3,$X$1,$AA4:$CU4)

top row AA3 - CU3 contains months.
below row AA4 - CU4 contains values
my objective is to sum all of the values for past months ( in comparison
to
current month, which is located in the cell X1)

Thanks, Gus




"SleazyBreezy" wrote:

Hi all,

Say I have a spreadsheet listed as follows:

A1 = 03/31/05
A2 = $5.00

B1 = 03/31/05
B2 = $10.00

C1 = 04/01/05
C2 = $20.00

The entire A column is named "Base_Date" and the entire B column is named
"Base_Amount".

I would like a formula that will sum values in the Amount column that
correspond with a specific date range, ie. March only. This formula must
reside in a different worksheet (within the same workbook).

I've tried the following:

=SUM(IF(Base_Date=DATEVALUE("03/01/2005"),IF(Base_Date=DATEVALUE("03/31/2005"),Base_Amount,0),0))

If I place the formula in a seperate worksheet from the data (as
intended),
its value is $35.00 (adds all values in column B) when I'm expecting it
to be
only $15.00.

If I place the formula in the same worksheet as the data (not in columns
A
or B), its value is reported as 0.

I'm sure I'm missing something miniscule here. Any suggestions?

TIA,
Sleazy

If the date in cell X1 is formated same way as above,
the formula does not work, why?.

It should. Try it like this:

=SUMIF($AA$3:$CU$3,"="&$X$1,$AA4:$CU4)

--
Biff
Microsoft Excel MVP


"Gus" wrote in message
...
The top raw is in the date format ( 1/1/2008 to display Jan-08).
If the date in cell X1 is formated same way as above, the formula does not
work, why?.
Once I change it to =39814 then the formula works.

Gus





"T. Valko" wrote:

top row AA3 - CU3 contains months.

In what form? Are they the month names as *TEXT* entrires: Jan, Feb, Mar?

Are they *DATES* formatted to display the month name?

when I change today's date to a number it works.

How are you changing today's date to a number? What number?

Maybe you want something like this:

=SUMPRODUCT(--(MONTH($AA$3:$CU$3)MONTH($X$1)),$AA4:$CU4)


--
Biff
Microsoft Excel MVP


"Gus" wrote in message
...
Hi,

I have a similar situation, but with data in rows.
Here is the formula which does not work for me (why?) but when I change
today's date to a number it works.

=SUMIF($AA$3:$CU$3,$X$1,$AA4:$CU4)

top row AA3 - CU3 contains months.
below row AA4 - CU4 contains values
my objective is to sum all of the values for past months ( in
comparison
to
current month, which is located in the cell X1)

Thanks, Gus




"SleazyBreezy" wrote:

Hi all,

Say I have a spreadsheet listed as follows:

A1 = 03/31/05
A2 = $5.00

B1 = 03/31/05
B2 = $10.00

C1 = 04/01/05
C2 = $20.00

The entire A column is named "Base_Date" and the entire B column is
named
"Base_Amount".

I would like a formula that will sum values in the Amount column that
correspond with a specific date range, ie. March only. This formula
must
reside in a different worksheet (within the same workbook).

I've tried the following:

=SUM(IF(Base_Date=DATEVALUE("03/01/2005"),IF(Base_Date=DATEVALUE("03/31/2005"),Base_Amount,0),0))

If I place the formula in a seperate worksheet from the data (as
intended),
its value is $35.00 (adds all values in column B) when I'm expecting
it
to be
only $15.00.

If I place the formula in the same worksheet as the data (not in
columns
A
or B), its value is reported as 0.

I'm sure I'm missing something miniscule here. Any suggestions?

TIA,
Sleazy

The SUMIF function can work with one criteria given. How can I modify a
formula by adding other functions to make the SUMIF work with TWO criterion
met?

Thanks



"SleazyBreezy" wrote:

Thank you very much! Thankfully, my date criteria is static, so the X2/Y2
variables are not needed. I used your formula to achieve the desired result
by moving the closing quotes as follows:

=SUMIF(Base_Date,"=03/01/2005",Base_Amount)-SUMIF(Base_Date,"03/31/2005",Base_Amount)


"Aladin Akyurek" wrote:

=SUMIF(Base_Date,"="&X2,Base_Amount)-SUMIF(Base_Date,""&Y2,Base_Amount)

where X2 houses the first day date of the month/year of interset like:
03/01/2005 and Y2 one of:

03/31/2005

=DATE(YEAR(X2),MONTH(X2)+1,0)

Hi

Up until XL2003, Sumif takes a single Criterion.
With XL2007, SUMIFS allows multiple criteria.

If you are using 2003 or lower, consider the use of Sumproduct.

=SUMPRODUCT((Range1=X1)*(Range2=Y1)*(Range3=Z1)*Ra nge4)
Where Range 4 is the values to be Summed, the other Ranges are what you are
wanting to match against certain values held in cells X1, Y1 and Z1.
Ensure the Ranges are of equal size, and in 2003 and lower, ranges cannot be
whole columns.

--
Regards
Roger Govier

"tywlam" wrote in message
...
The SUMIF function can work with one criteria given. How can I modify a
formula by adding other functions to make the SUMIF work with TWO
criterion
met?

Thanks



"SleazyBreezy" wrote:

Thank you very much! Thankfully, my date criteria is static, so the X2/Y2
variables are not needed. I used your formula to achieve the desired
result
by moving the closing quotes as follows:

=SUMIF(Base_Date,"=03/01/2005",Base_Amount)-SUMIF(Base_Date,"03/31/2005",Base_Amount)


"Aladin Akyurek" wrote:

=SUMIF(Base_Date,"="&X2,Base_Amount)-SUMIF(Base_Date,""&Y2,Base_Amount)

where X2 houses the first day date of the month/year of interset like:
03/01/2005 and Y2 one of:

03/31/2005

=DATE(YEAR(X2),MONTH(X2)+1,0)

Thanks Roger. But it just couldn't work! Let me take an example below:

Col.A Col.B Col.C
Row1 Project Date Amount
Row2 AAA 01/03/2008 200
Row3 BBB 01/03/2008 100
Row4 AAA 15/03/2008 20
Row5 CCC 02/03/3008 10
Row6 DDD 01/04/2008 200
Row7 CCC 27/03/2008 130
Row8 AAA 05/04/2008 30

I'd like to sum the values in Col.C with project name of AAA in Col.A and
date of March 2008 in Col.B. I write the the formula:
=SUMPRODUCT((A1:A8="AAA")*(B1:B8="**/03/****")*(C1:C8)) but it gives the
result of zero! The correct answer should be 220. (200+20)

Please help!

Regards,
Terry


"Roger Govier" wrote:

Hi

Up until XL2003, Sumif takes a single Criterion.
With XL2007, SUMIFS allows multiple criteria.

If you are using 2003 or lower, consider the use of Sumproduct.

=SUMPRODUCT((Range1=X1)*(Range2=Y1)*(Range3=Z1)*Ra nge4)
Where Range 4 is the values to be Summed, the other Ranges are what you are
wanting to match against certain values held in cells X1, Y1 and Z1.
Ensure the Ranges are of equal size, and in 2003 and lower, ranges cannot be
whole columns.

--
Regards
Roger Govier

"tywlam" wrote in message
...
The SUMIF function can work with one criteria given. How can I modify a
formula by adding other functions to make the SUMIF work with TWO
criterion
met?

Thanks



"SleazyBreezy" wrote:

Thank you very much! Thankfully, my date criteria is static, so the X2/Y2
variables are not needed. I used your formula to achieve the desired
result
by moving the closing quotes as follows:

=SUMIF(Base_Date,"=03/01/2005",Base_Amount)-SUMIF(Base_Date,"03/31/2005",Base_Amount)


"Aladin Akyurek" wrote:

=SUMIF(Base_Date,"="&X2,Base_Amount)-SUMIF(Base_Date,""&Y2,Base_Amount)

where X2 houses the first day date of the month/year of interset like:
03/01/2005 and Y2 one of:

03/31/2005

=DATE(YEAR(X2),MONTH(X2)+1,0)

Hi

It will work, if you need to change the formula slightly.
dates are stored as serial numbers, hence they will never be "seen" as **
/03/ ****.
You need to compare the Text(date,"mmyyyy") to get your result.

=SUMPRODUCT((A1:A8="AAA")*(TEXT(B1:B8,"mmyyyy")="0 32008")*C1:C8)

You don't need parentheses around the C values, as they are your data and
are not being used in any comparison.
Including them, will nit prevent the formula working - they are just not
necessary.

--
Regards
Roger Govier

"tywlam" wrote in message
...
Thanks Roger. But it just couldn't work! Let me take an example below:

Col.A Col.B Col.C
Row1 Project Date Amount
Row2 AAA 01/03/2008 200
Row3 BBB 01/03/2008 100
Row4 AAA 15/03/2008 20
Row5 CCC 02/03/3008 10
Row6 DDD 01/04/2008 200
Row7 CCC 27/03/2008 130
Row8 AAA 05/04/2008 30

I'd like to sum the values in Col.C with project name of AAA in Col.A and
date of March 2008 in Col.B. I write the the formula:
=SUMPRODUCT((A1:A8="AAA")*(B1:B8="**/03/****")*(C1:C8)) but it gives the
result of zero! The correct answer should be 220. (200+20)

Please help!

Regards,
Terry


"Roger Govier" wrote:

Hi

Up until XL2003, Sumif takes a single Criterion.
With XL2007, SUMIFS allows multiple criteria.

If you are using 2003 or lower, consider the use of Sumproduct.

=SUMPRODUCT((Range1=X1)*(Range2=Y1)*(Range3=Z1)*Ra nge4)
Where Range 4 is the values to be Summed, the other Ranges are what you
are
wanting to match against certain values held in cells X1, Y1 and Z1.
Ensure the Ranges are of equal size, and in 2003 and lower, ranges cannot
be
whole columns.

--
Regards
Roger Govier

"tywlam" wrote in message
...
The SUMIF function can work with one criteria given. How can I modify
a
formula by adding other functions to make the SUMIF work with TWO
criterion
met?

Thanks



"SleazyBreezy" wrote:

Thank you very much! Thankfully, my date criteria is static, so the
X2/Y2
variables are not needed. I used your formula to achieve the desired
result
by moving the closing quotes as follows:

=SUMIF(Base_Date,"=03/01/2005",Base_Amount)-SUMIF(Base_Date,"03/31/2005",Base_Amount)


"Aladin Akyurek" wrote:

=SUMIF(Base_Date,"="&X2,Base_Amount)-SUMIF(Base_Date,""&Y2,Base_Amount)

where X2 houses the first day date of the month/year of interset
like:
03/01/2005 and Y2 one of:

03/31/2005

=DATE(YEAR(X2),MONTH(X2)+1,0)

It works when both criteria and formula are in the same worksheet. Thank
you. But when the formula is not in the same worksheet, it give all zero
results!! I've checked the formula carefully and also used the functions bar
dialog box to set up my formula. It still doesn't work! Do you know why?

Regards,


"Roger Govier" wrote:

Hi

It will work, if you need to change the formula slightly.
dates are stored as serial numbers, hence they will never be "seen" as **
/03/ ****.
You need to compare the Text(date,"mmyyyy") to get your result.

=SUMPRODUCT((A1:A8="AAA")*(TEXT(B1:B8,"mmyyyy")="0 32008")*C1:C8)

You don't need parentheses around the C values, as they are your data and
are not being used in any comparison.
Including them, will nit prevent the formula working - they are just not
necessary.

--
Regards
Roger Govier

"tywlam" wrote in message
...
Thanks Roger. But it just couldn't work! Let me take an example below:

Col.A Col.B Col.C
Row1 Project Date Amount
Row2 AAA 01/03/2008 200
Row3 BBB 01/03/2008 100
Row4 AAA 15/03/2008 20
Row5 CCC 02/03/3008 10
Row6 DDD 01/04/2008 200
Row7 CCC 27/03/2008 130
Row8 AAA 05/04/2008 30

I'd like to sum the values in Col.C with project name of AAA in Col.A and
date of March 2008 in Col.B. I write the the formula:
=SUMPRODUCT((A1:A8="AAA")*(B1:B8="**/03/****")*(C1:C8)) but it gives the
result of zero! The correct answer should be 220. (200+20)

Please help!

Regards,
Terry


"Roger Govier" wrote:

Hi

Up until XL2003, Sumif takes a single Criterion.
With XL2007, SUMIFS allows multiple criteria.

If you are using 2003 or lower, consider the use of Sumproduct.

=SUMPRODUCT((Range1=X1)*(Range2=Y1)*(Range3=Z1)*Ra nge4)
Where Range 4 is the values to be Summed, the other Ranges are what you
are
wanting to match against certain values held in cells X1, Y1 and Z1.
Ensure the Ranges are of equal size, and in 2003 and lower, ranges cannot
be
whole columns.

--
Regards
Roger Govier

"tywlam" wrote in message
...
The SUMIF function can work with one criteria given. How can I modify
a
formula by adding other functions to make the SUMIF work with TWO
criterion
met?

Thanks



"SleazyBreezy" wrote:

Thank you very much! Thankfully, my date criteria is static, so the
X2/Y2
variables are not needed. I used your formula to achieve the desired
result
by moving the closing quotes as follows:

=SUMIF(Base_Date,"=03/01/2005",Base_Amount)-SUMIF(Base_Date,"03/31/2005",Base_Amount)


"Aladin Akyurek" wrote:

=SUMIF(Base_Date,"="&X2,Base_Amount)-SUMIF(Base_Date,""&Y2,Base_Amount)

where X2 houses the first day date of the month/year of interset
like:
03/01/2005 and Y2 one of:

03/31/2005

=DATE(YEAR(X2),MONTH(X2)+1,0)

Another problem: If I put a Cell number that is reference to a text in
another cell in substitute of direct text "AAA" in the formula, the result
becomes zero again! How do I format the cell or change the formula?

Regards,
Terry


"Roger Govier" wrote:

Hi

It will work, if you need to change the formula slightly.
dates are stored as serial numbers, hence they will never be "seen" as **
/03/ ****.
You need to compare the Text(date,"mmyyyy") to get your result.

=SUMPRODUCT((A1:A8="AAA")*(TEXT(B1:B8,"mmyyyy")="0 32008")*C1:C8)

You don't need parentheses around the C values, as they are your data and
are not being used in any comparison.
Including them, will nit prevent the formula working - they are just not
necessary.

--
Regards
Roger Govier

"tywlam" wrote in message
...
Thanks Roger. But it just couldn't work! Let me take an example below:

Col.A Col.B Col.C
Row1 Project Date Amount
Row2 AAA 01/03/2008 200
Row3 BBB 01/03/2008 100
Row4 AAA 15/03/2008 20
Row5 CCC 02/03/3008 10
Row6 DDD 01/04/2008 200
Row7 CCC 27/03/2008 130
Row8 AAA 05/04/2008 30

I'd like to sum the values in Col.C with project name of AAA in Col.A and
date of March 2008 in Col.B. I write the the formula:
=SUMPRODUCT((A1:A8="AAA")*(B1:B8="**/03/****")*(C1:C8)) but it gives the
result of zero! The correct answer should be 220. (200+20)

Please help!

Regards,
Terry


"Roger Govier" wrote:

Hi

Up until XL2003, Sumif takes a single Criterion.
With XL2007, SUMIFS allows multiple criteria.

If you are using 2003 or lower, consider the use of Sumproduct.

=SUMPRODUCT((Range1=X1)*(Range2=Y1)*(Range3=Z1)*Ra nge4)
Where Range 4 is the values to be Summed, the other Ranges are what you
are
wanting to match against certain values held in cells X1, Y1 and Z1.
Ensure the Ranges are of equal size, and in 2003 and lower, ranges cannot
be
whole columns.

--
Regards
Roger Govier

"tywlam" wrote in message
...
The SUMIF function can work with one criteria given. How can I modify
a
formula by adding other functions to make the SUMIF work with TWO
criterion
met?

Thanks



"SleazyBreezy" wrote:

Thank you very much! Thankfully, my date criteria is static, so the
X2/Y2
variables are not needed. I used your formula to achieve the desired
result
by moving the closing quotes as follows:

=SUMIF(Base_Date,"=03/01/2005",Base_Amount)-SUMIF(Base_Date,"03/31/2005",Base_Amount)


"Aladin Akyurek" wrote:

=SUMIF(Base_Date,"="&X2,Base_Amount)-SUMIF(Base_Date,""&Y2,Base_Amount)

where X2 houses the first day date of the month/year of interset
like:
03/01/2005 and Y2 one of:

03/31/2005

=DATE(YEAR(X2),MONTH(X2)+1,0)