conditional formula based on dates

  • Thread starter Thread starter filups
  • Start date Start date
F

filups

I would like to write a formula that adds numbers between certain dates.
Lets say I have this worksheet:

A B
01-01-08 5
01-02-08 7
.......
11-28-08 3
11-29-08 11

In another worksheet, I would like to be able to type in two dates , and
have excel look back at the original worksheet and add all the values in
column B between the two dates listed. Is that possible?

The date ranges will be added anyway, so using them to do the formula would
be much easier than going back and highlighting the data range every time.
 
I would like to write a formula that adds numbers between certain dates.
Lets say I have this worksheet:

A B
01-01-08 5
01-02-08 7
......
11-28-08 3
11-29-08 11

In another worksheet, I would like to be able to type in two dates , and
have excel look back at the original worksheet and add all the values in
column B between the two dates listed. Is that possible?

The date ranges will be added anyway, so using them to do the formula would
be much easier than going back and highlighting the data range every time.
Click to expand...

If your start date is in cell D10 and your end date is in cell D11
you can try the following formula in cell to get the sum:

=SUMPRODUCT((Sheet1!A1:A100>=D10)*(Sheet1!A1:A100<=D11)*(Sheet1!B1:B100))

Change the 100 to fit the number of data that you have in the original
worksheet, i.e. "Sheet1".

Regarding "between": If you dont want to include the start date and/or
end date, change >= and <= to > and < respectively.

Hope this helps / Lars-Åke
 
try this
Sheet 1 has data ( col A dates & Col B numbers )

assumed sheet 2 cell A1 & B1 has date.
in Cell C1 put this formula

=SUMPRODUCT(--(sheet1!A1:A4>=A1),--(sheet1!A1:A4<=B1)*(sheet1!B1:B4))
 
Hi,

In 2007
=SUMIFS(Sheet1!B1:B18,Sheet1!A1:A18,">="&A1,Sheet1!A1:A18,"<="&B1)
In 2003
=SUMPRODUCT(Sheet1!B1:B18,--(Sheet1!A1:A18>=A1),--(Sheet1!A1:A18<=B1))

If this helps, please click the Yes button

Cheers,
Shane Devenshire
 
Hi,

You can also use either of the following:

1. DSUM function. Please read up on this in Excel's Help menu;
2. =SUM(IF((A2:A200>=start_date)*(A2:A200<=start_date),B2:B100)). This is
an array formula - so please use a Ctrl+Shift+Enter.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top