date range and value return

[bennyis2010]

hi guys,
I am stuck on an excel problem.
I have thousands of cells of data.
In one column I have manually entered dates. Sometimes the date is a single day (1/1/2003). Other times it is a manually entered date range (1/1/2003-5/1/2003). A few columns later each entry has a numerical value to capture the scale of something I am measuring. The numbers are like: 10, 126, 480; and so on.
Elsewhere in the spread sheet I want to return a single value for the sum of all the numbers that correspond to a specific date,
So for example,

TAB 1
DATE VALUE
1/1/2003 20
1/1/2003-5/1/2003 10 (i.e. 10 per day)
1/1/2003-20/6/2007 10 (i.e. 10 per day)
and so on....

TAB 2
DATE VALUE (I need the formula to calculate below)
1/1/2003 40
2/1/2003 20
3/1/2003 20
4/1/2003 20
5/1/2003 20
6/1/2003 10

Is that clear?
Can you help me with that?

ben

 

[sheetspread]

With text to columns you can split the dash delimited cells, F5-special-blanks, type = left arrow, ctrl-enter, then:

Excel 2010
	A	B	C
1	Date		Number
2	1/1/2003	1/1/2003	20
3	1/1/2003	1/5/2003	10
4	1/1/2003	6/20/2007	10
5
6
7	1/1/2003	40
8	1/2/2003	20
9	1/3/2003	20
10	1/4/2003	20
11	1/5/2003	20
12	1/6/2003	10
Sheet7 (2)

Cell Formulas
Range	Formula
B7	=SUMIFS($C$2:$C$4,$A$2:$A$4,"<="&A7,$B$2:$B$4,">="&A7)

 

[bennyis2010]

Thank you.
This worked perfectly. Much appreciated.

I wonder if you can help me take this one step further.

Imagine I want to do exactly the same thing as above, but add one extra element.

I want to add values for a variety of dates and date ranges (as above) but ONLY for those with a certain value in another column.

How would I add an additional clause to the above formula to only return the total value for those within the dates specified and that contain in another column, for example the text "Apples" (bot not other words like "Oranges" etc).

Your help is greatly appreciated.

ben

 

[sheetspread]

This:

Excel 2010
	A	B	C	D
1	Fruit	Date		Number
2	Oranges	1/1/2003	1/1/2003	20
3	Apples	1/1/2003	1/5/2003	10
4	Oranges	1/1/2003	6/20/2007	10
5
6
7	1/1/2003	10
8	1/2/2003	10
9	1/3/2003	10
10	1/4/2003	10
11	1/5/2003	10
12	1/6/2003	0
Sheet7 (2)

Cell Formulas
Range	Formula
B7	=SUMIFS($D$2:$D$4,$B$2:$B$4,"<="&A7,$C$2:$C$4,">="&A7,$A$2:$A$4,"Apples")