# Vlookup of a rolling sum

#### Mackpazz

##### New Member
I am trying to do a vlookup of a sum within a range. I have a spreadsheet with columns of numbers (dollar values) with a column of dates at the end of the range. I am trying to lookup the cooresponding date when all of the dollars are spent in a given row.

Example:

Column A - Column B - Column C
Row 1 100 - 0 - 6/30/05
Row 2 0 - 100 - 7/31/05
Row 3 150 - 0 - 8/31/05
Row 4 500 - 0 - 9/30/05
Row 5 0 - 250 - 10/31/05
Row 6 0 - 0 - 11/30/05

Totals 750 350

I want to lookup the total of column A (i.e. 750) and return the date in column C in the cooresponding month where the sum of column A matches the total, basically what month are we done spending money (i.e.9/30/05)

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try this

=SUMPRODUCT(MAX((A1:A6>0)*C1:C6))

format as date

Thanks much...works great. You have several of us Excel geeks in awe!

Can you futher explain what that formula is doing? Again, it works great but we can't follow the logic.

There appears to only be one array in the SUMPRODUCT? Doesn't the MAX require two comparable results?

The statement (A1:A6>0) is a mystery? Doesn't it need an IF?

The various pieces of the formula do not work independently, but do collectively.

Again, the formula works perfectly, we are just trying to understand why it works. Thanks.

Replies
2
Views
117
Replies
1
Views
123
Replies
2
Views
57
Replies
19
Views
378
Replies
3
Views
148

1,203,605
Messages
6,056,234
Members
444,852
Latest member
MJaspering

### 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.

### Which adblocker are you using?

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