lookup today +7 and return values and total

IMRAY

New Member
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

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
ABCDEFGHI
1AAAA13/03/2013rrrrr0.53Col DCol E
2BBBB27/03/2013nnnnn7489Excel 2007+
3cccc28/03/2013llllll0.5289Excel 2000+
4dddd15/04/2013zzzzz23

</TBODY>
Sheet1

Worksheet Formulas
CellFormula
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)))

</TBODY>

<TBODY>
</TBODY>

Hope that helps.

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 ?

IMRAY,

Try this......

Drag G2 Formula to H2

Excel 2007
ABCDEFGH
1AAAA13/03/2013rrrrr0.53Col DCol E
2BBBB27/03/2013nnnnn740.52
3cccc28/03/2013llllll0.52
4dddd15/04/2013zzzzz23
Sheet1
Cell Formulas
RangeFormula
G2=SUMPRODUCT(D\$1:D\$4,--(\$B\$1:\$B\$4<(TODAY()+8)),--(\$B\$1:\$B\$4>(TODAY()-1)))

Hope that is more like it.

=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!

Replies
11
Views
313
Replies
4
Views
112
Replies
3
Views
236
Replies
6
Views
229
Replies
3
Views
152

1,196,413
Messages
6,015,118
Members
441,872
Latest member
Jyyyyyyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back