lookup today +7 and return values and total

[IMRAY]

Hello

I am wondering if someone can help

I have a sheet with information set as below

A B C D E

1 AAAA 13/3/2013 rrrrr 0.5 3
2 BBBB 27/3/2013 nnnn 0.7 4
3 ccccc 28/3/2013 llllll 0.5 2
4 dddd 15/04/2013 zzzz 1 3

What I am looking for is something that will take todays date +7days and give me totals for columns D+E.

I have tried using Vlookup (today()+7 but unsure of what follows as I am only getting one value

Thanks

 

[Snakehips]

IMRAY,

*I am assuming that you want the totals from D & E where the date is less than or equal to Today() +7 ???????/

Below are 2 formulas that will do this. If you are using 2007 then use the COUNTIF otherwise use SUMPRODUCT>

You wil need to extend the ranges to suit your data. If I am wrong with my first statement* then you will just need to change tyhe operator in the formula to suit.

Excel 2007

	A	B	C	D	E	F	G	H	I
1	AAAA	13/03/2013	rrrrr	0.5	3		Col D	Col E
2	BBBB	27/03/2013	nnnnn	7	4		8	9	Excel 2007+
3	cccc	28/03/2013	llllll	0.5	2		8	9	Excel 2000+
4	dddd	15/04/2013	zzzzz	2	3

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula G2 =SUMIF($B$1:$B$4,"<="&TODAY()+"7",D1:D4) H2 =SUMIF($B$1:$B$4,"<="&TODAY()+"7",E1:E4) G3 =SUMPRODUCT($D$1:$D$4,--($B$1:$B$4<=(TODAY()+7))) H3 =SUMPRODUCT($E$1:$E$4,--($B$1:$B$4<=(TODAY()+7))) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Hope that helps.

 

[IMRAY]

Hi thanks for the reply what I am trying to do is using todays date 28/3 and the next 7 days up to the 4/4/ sum up D + E as you have said.
I did change the operator to greater than but not working as I would have though as is your example going from yesterday 27/3 back 7 days to 21/3 i would be looking for the results to be 7 & 4 only? as the reply you have entered is taking row 1, 2 & 3 into account.

Sorry maybe my explanation was not as clear as it should have been.
so for what I am looking for would be only to return the vaules from row 3.
and if I was to change the date on row 4 to 2/4 it would include this also ?

 

[Snakehips]

IMRAY,

Try this......

Drag G2 Formula to H2

Excel 2007
	A	B	C	D	E	F	G	H
1	AAAA	13/03/2013	rrrrr	0.5	3		Col D	Col E
2	BBBB	27/03/2013	nnnnn	7	4		0.5	2
3	cccc	28/03/2013	llllll	0.5	2
4	dddd	15/04/2013	zzzzz	2	3
Sheet1

Cell Formulas
Range	Formula
G2	=SUMPRODUCT(D$1:D$4,--($B$1:$B$4<(TODAY()+8)),--($B$1:$B$4>(TODAY()-1)))

Hope that is more like it.

 

[IMRAY]

=SUMPRODUCT(D$1:D$5+E1:E5,--($B$1:$B$5<(TODAY()+8)),--($B$1:$B$5>(TODAY()-1)))

I did change it slightly to add both columns but works a treat you are a star!

Thank you ever so much I would never have got that!