sum multiple rows vertical lookup

[tim browne]

Hello,

I am creating a vertical lookup for which I want to sum up the data to a corresponding date over several days, formula I have now is

=VLOOKUP(B813 (date);pickups!A1:ST10000;2+3+4+5+6+7+8+9+10;FALSE)

each + will correcpond to a different column index but it looks like its not summing up...
even though column indexes 2 to 6 have value 0 for a specific lookup value, column 8 to 10 contain data but the outcome in the cell now remains 0. Anybody can correct my lookup?

Thank you in advance.

Best Regards,

Tim

[bebo021999]

Not testing yet, but try:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[mcmahobt]

You could write a formula that sums 10 individual VLOOKUPS, but there is most likely a simpler method. To help you more, we'd need to know how your data is setup. Can you upload a sample workbook?

[Jonmo1]

I would do it like this

=SUM(INDEX(pickups!B1:J10000;MATCH(B813;pickups!A1:A10000;0);0))

[tim browne]

Online exmaple.xlsx

I hope this file is correctly attached.
First of all WOW, what a fast replies and thank you all! My first time here and I am impressed by the response time... Tried the 2 options provided unfortunately (i did not get them) work.

Attached version short example.
For Columns E,F,G I need teh sum of several dates from Tab Pickups
Tab Pickups is daily updated by macro, as Is Column C in Overview BRUC! and column D will refer to that (=)
In overview BRUC! I need to import the lookup values in Column B from pickup! corresponding to the number of columns mentioned above in Column E, F, G (e.g. 7, 14, 31 days)
As you might see in Pickups! the macro inputs new data (on the date the macro runs) inserting data in column B.

I hope I make myself clear, not easy to explain clearly...

[Jonmo1]

It's difficult to see what results you want in which cells...

Can you manually fill out formulas (setting the desired ranges by hand) in a small portion of that, say D3:G5 ?

[bebo021999]

One way with SUMPRODUCT:

Pickup to day, in D3:

=SUMPRODUCT((pickups!$A$2:$A$404=$B3)*(pickups!$B$1:$Y$1=$B3)*pickups!$B$2:$Y$404)

Pickup last 7 days, in E3:

=SUMPRODUCT((pickups!$A$2:$A$404=$B3)*(pickups!$B$1:$Y$1<=$B3)*(pickups!$B$1:$Y$1>=$B3-7)*pickups!$B$2:$Y$404)

Adjust value in red with 14 or 31

[tim browne]

So, the pickups (E-G) are coming from TAB Pickup. only thing is that the formula needs to do a Vlookup from value row A in tab Overview BRUC when calculating values as the dates might change and not always be in the same row.

Online exmaple.xlsx

[mcmahobt]

Along the same lines as Bebo's, but column G's formula takes into account the last month's date relative to today's, so that months with less than 31 days doesn't throw off the formula.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Jonmo1]

Pretty much the same as my original answer.
You just have to adjust the columns for each formula in column E F and G

E3: =SUM(INDEX(pickups!$B$2:$H$361,MATCH($B3,pickups!$A$2:$A$361,0),0))
F3: =SUM(INDEX(pickups!$B$2:$O$361,MATCH($B3,pickups!$A$2:$A$361,0),0))
G3: =SUM(INDEX(pickups!$B$2:$Y$361,MATCH($B3,pickups!$A$2:$A$361,0),0))

Then fill all 3 down.

[tim browne]

WOW, going over my head...
maybe my example upload was not correct, because the pickup tab's data has much more data, this is a shortened version due to the fact that the upload was too big.
is has data from 2010 until 2030 in rows and all previous records from 2 years ago in the columns. However the calculation should be made on the count of rows and not per definition the exact number of days (new columns only insert when the macro is run and that for instance doesn't happen in weekends)...
Also the dates in Tab daily overview will usually be the same per standard but might change in the future that why I was looking for the Vlookup...
I thought indeed of summing all individual vlookups per column (meaning the month will have the sum of 31 vlookups), but indeed there shgould be an easier way.
Also the table array from the lookup will be the whole TAB pickups, starting with lookup value in column A but with unlimited ending, can that be set in a formula?

[bebo021999]

I think SUMPRODUCT as per mine and mcmahobt's introduction (#7 and #9) is able to update your data growing up.

Change the current range $B$2:$Y$361 to as far as you need ($B$2:$ZZ$361 for example).