Using SUMIF to add values from "last seven days"

[nubbin]

Ok, please take a look at this spreadsheet I'm making:
http://www.nubbin.org/Training.xls
(same as the attached file)
And look at the "Totals" worksheet within it. Cell G5, to be exact.
Here's my goal: I want to be able to look at the data on the "Input
sheet, and add up the miles I've run in the last 7 days.
I have two problems.
1) The formula in "Totals!G5" SHOULD (I think) be able to add up al
data regardless of "Activity" - but it doesn't.
2) I need to know how to add *2* "criteria" to my SUMIF formula
stipulating the dates, AND the activity.

Any help would be MUCH appreciated

Attachment filename: training.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=50640

 

[Norman Harker]

Hi nubbin!

You have no data as to what amounts per day apply to what:

Here's the type of layout for just one lot of data:

Date Distance
30/03/2004 2
31/03/2004 6
1/04/2004 15
2/04/2004 0
3/04/2004 3
4/04/2004 2
5/04/2004 4
6/04/2004 7
7/04/2004 4
8/04/2004 3
9/04/2004 2
10/04/2004 9
11/04/2004 6
12/04/2004 15


And my formula:
=SUMPRODUCT(--(($A$2:$A$15)>=TODAY()-7),--(($A$2:$A$15)<TODAY()),($B$2:$B$15))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi
based on your example use
=SUMPRODUCT(--(Input!A2:A11>=TODAY()-7),--(Input!A2:A11<TODAY()),Input!
E2:E11)