Vlookup with sum function

[N0t Y0urs]

Not sure if I can do what I want but I think I want a sumif v lookup formula.

This is my scenario I have 3 columns in my range. First one being my reference number, my second column has a weekly value in it and my 3rd column is a running balance. So what I want is a running total of the weekly value at a reference point.

Let assume this i have 100 entries therefore each weekly value starts at 100 and doubles each line so I want the value at level 4 then it’s value is 3100

I’ve attached an image to assist
Any ideas

 

[etaf]

in your profile you have

Office Version

1. 365
2. 2021
3. 2019
4. 2016
5. 2013

Which version are you using , as the solution will change a lot , later versions of excel have functions which will NOT work on earlier version

i'm not sure what you need from the explanation or image ,I dont see the example shown

Let assume this i have 100 entries therefore each weekly value starts at 100 and doubles each line so I want the value at level 4 then it’s value is 3100

i see a weekly reference going up in 5's
then a WD column which I assume is the weekly value
no idea what Level 4 means in this context

A SUMIFS()
may do what you want

So what I want is a running total of the weekly value at a reference point.

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

 

[N0t Y0urs]

Thanks for the reply. I’ve created a mini version of the data. I’m using 365.

I’ve used Google Drive and tried to explain what I need within the data

Google Sheets - create and edit spreadsheets online, for free.

Create a new spreadsheet and edit with others at the same time -- from your computer, phone or tablet. Get stuff done with or without an internet connection. Use Sheets to edit Excel files. Free from Google.

docs.google.com

The WD is weekly withdrawals so I want to know how much has been withdrawn to a certain date then add that to the running balance figure. Does that make sense?

 

[etaf]

the link requires permission to open

sumifs() or sumif() is probably the way to go

in your image above , i dont see a date column

I want to know how much has been withdrawn to a certain date

otherwise the SUMIF(S) () would work with a date range that could be entered into cells to show start / end date
if you want the running balance to be updated and changed , then you may need VBA to change that value , if its not already a function that can be added to

 

[N0t Y0urs]

the link requires permission to open

sumifs() or sumif() is probably the way to go

in your image above , i dont see a date column

otherwise the SUMIF(S) () would work with a date range that could be entered into cells to show start / end date
if you want the running balance to be updated and changed , then you may need VBA to change that value , if its not already a function that can be added to

I have changed the permissions on the link and will give the date range a go. I did figure out a long way round being a second hidden column that adds like this

Sum($d$5:$d25)+e25 and then I use the lookup to get the value but now I would like to figure out the easy way ?

 

[Peter_SSs]

As I understand the problem, it seems like SUMIFS (or SUMIF could be used), as suggested by @etaf, is the way to go.

Does this do what you want?

=SUMIFS(D6:D25,C6:C25,"<="&C32)

 

[N0t Y0urs]

As I understand the problem, it seems like SUMIFS (or SUMIF could be used), as suggested by @etaf, is the way to go.

Does this do what you want?

=SUMIFS(D6:D25,C6:C25,"<="&C32)

Thaa add no you so much everyone that’s what I needed. Now I am just going to work it backwards so I understand what you did.

Thanks again

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[etaf]

you are welcome, glad its working ok

 

[N0t Y0urs]

Just wanted to say I’ve thrown it into my big spreadsheet and it’s working perfectly and I understand the logic now as well.

I would love to get my head around all excels capabilities as I think I could achieve some pretty awesome things if I had the know how