Re: Sum of named ranges conditional to date?
- From: NAT <NAT@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 11 Jun 2009 16:45:01 -0700
I think my request is simpler than all I've seen here and yet I can't get it
to work (it always gives me an anser of 0). In column A I have dates in
mm/dd/yyyy format, in column B I have amounts. I only want to sum the
amounts in column B if they correspond to a date which is less than or equal
today's date. Here's what I came up with (which doesn't work):
=SUMIF(A2:A28, "<="&TODAY(), B2:B28)
Can anyone tell me what I'm doing wrong?
"T. Valko" wrote:
.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" <Gus@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1B71E965-D30F-433E-AA0A-D56DEC5407AD@xxxxxxxxxxxxxxxx
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" <Gus@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8A4F7C4E-FAE6-42F2-8EA1-78257FB0FD36@xxxxxxxxxxxxxxxx
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
- Follow-Ups:
- Re: Sum of named ranges conditional to date?
- From: T. Valko
- Re: Sum of named ranges conditional to date?
- Prev by Date: Re: Trying to highlight cells that have the same value as the acti
- Next by Date: RE: Sort Block of Data
- Previous by thread: How to import PDF into Excel?
- Next by thread: Re: Sum of named ranges conditional to date?
- Index(es):
Relevant Pages
|
