SumIF a date is with in 90 days of todays date before or after

[fireguy7]

This one should be easy for you all.

I have todays date in cell A2

I have a range of dates in a row 4 and numbers below each date in row 6.

What i want is to Sum the numbers below the dates in row 6 if the date in row 4 is with in 90 days before or after the date in cell A2.

Example

todays date is 10-29-09

05-15-09 , 08-15-09 , 09-15-09 , 12-15-09 , 04-15-10
1 , 2 , 1 , 1 , 1


The answer i should get is 4 since only 3 dates fall 90 days before or after todays date.

Thoughts?

[rwgrietveld]

Try

=SUMPRODUCT(--($A$4:$E$4>$C$2),--($A$4:$E$4<$D$2),$A$5:$E$5)
which is similar to =SUMPRODUCT(--($A$4:$E$4>(NOW()-90)),--($A$4:$E$4<(NOW()+90)),$A$5:$E$5)

See the attachement.

Note: In the attachement I have set the format of the dates to numeric (,) values. You can change these back to Date Format.

In excel 2007 you can use SUMIFS. I do not have that luxery

[zbor]

Try this:

=SUMPRODUCT(--(ABS(C4:AH4-$C$1)<90),C6:AH6)

(adjust ranges.... C4-AH4 are dates, C1 is today, C6:AH6 are values)

[fireguy7]

simple and to the point i like

Thanks again.

[bcullers]

I need to only include data from rows with a date less than six weeks ago? I've been trying to subtract from TODAY, but getting errors. Anyone have a solution?

[zbor]

bcullers... please check the rules:

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

[zbor]

And also some useful tips:

Want to get your question answered quickly?

- Ensure your question is not too vague. Don't assume anyone is familiar with your problem. While you can upload small attachments, describe your problem in the body of the post. We are fortunate to have several Excel gurus, but few mind-readers.

- On the other hand, skip irrelevant details. Be descriptive and concise. Short, direct, and to-the-point questions with apt thread titles are almost always answered promptly.

- Keep the scope reasonably narrow. Questions like, "How do I set up an accounting system in Excel?" might be a long time waiting.

- Explain what you've already tried. ("Calculation is set to automatic, but formulas still don't compute") so helpers don't waste your time or theirs.

- Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
If your question has not been answered within a day, consider adding another post with any additional information you believe is relevant. If you think your post is good as is, just reply to your own thread with the words "Bump no response", which will bring it to the top of the forum.