Excel, Select value based on date, to lookup within daterange?

Question

I have a small Excel problem.

In my spreadsheet I have a column for Start-date and a End-date and a value. I would like to summarize all values that occur on the same date.

Example, Between January 15th and January 20th I have the value of 20 units. And between January 19th and January 22nd I have the value of 25 units. So my plan is to make a list, by date showing the amount of units per day. Like so:

January 15th: 20

January 16th: 20

January 17th: 20

January 18th: 20

January 19th: 45

January 20th: 45

January 21st: 25

January 22nd: 25

Is this possible with normal formulas?

.f.

Answer 1

fstran,

jmorge is right on this.  you would need to provide how your data is setup...you seem to have provided the result you are looking for.

right now it looks like 1/15/8 alone has 20 units.

and 1/19/8 by itself has 45 units, but you say theres only 25 between 19th and 22nd.

if sumif() isnt working, then sumproduct() usually works.

Answer 2

You might look into using the Pivot Table feature for this.  It would work perfectly for what you're trying to do.  Just select the Pivot Table option and the "wizard" will walk you through the basics of it.

without seeing what you're data looks like it's hard to tell but a SUMIF formula might work as well.  The basic syntact is

=SUMIF(range of data, critieria, sumrange)

example:

=SUMIF(A1:C50, "01/15/08", C1:C50)