Rolling Total for Each Warehouse Inventory

[petrodude]

Help! I'm stuck.

I am trying to create a rolling total for each of my 13 warehouses' on hand inventory at the end of each month in Column I

The idea is that if, for a row,

Item = Item

Month – 1 = Month
(Month "0" is the starting inventory at the beginning of the year, Month "1" is January, etc)

Whs = Whs

Then, SUM Net Inventory Cells

Else, ‘’


Here are some screenshots of sample data:
Data filtered for Whs "300" and Item

Data Filtered just for Item

Thank you in advance!

 

[petrodude]

Reposting hoping to get some new eyes on a previous post with no community solutions.

I am trying to create a rolling total for each of my 13 warehouses' on hand inventory at the end of each month in Column I

The idea is that if, for a row,

Item = Item

Month – 1 = Month
(Month "0" is the starting inventory at the beginning of the year, Month "1" is January, etc)

Whs = Whs

Then, SUM Net Inventory Cells

Else, ‘’


Here are some screenshots of sample data:
Data filtered for Whs "300" and Item

Data Filtered just for Item

Thank you in advance!

 

[SanjayGMusafir]

Your idea looks quite simple for SUMIFS formula. What's the challenge?

Share XL2BB of your real data to work on.

 

[SanjayGMusafir]

Almost 2 years back @Peter_SSs gave me fantastic solution in this thread. I think you are also trying to find something similar.

You must also check the solution @Alex Blakenburg gave. That is bit long but easy to understand.

Do check this thread if it helps you in some way. Else we are here to help you.

Referencing cell one above the current row in a structured table

Hi Experts, Got stuck up with a situation where I want to tell excel to add values in a Column from Header to the row one above current row. Unable to figure out how to reference a cell above in a structured table. The formula I'm currently using is -...

www.mrexcel.com

 

[Peter_SSs]

Reposting hoping to get some new eyes on a previous post with no community solutions.

However, #12 of the Forum Rules says not to post duplicate questions so I have combined the two threads. That rule mentions "bumping" your thread which replying to it yourself does. If you have not received any replies after a period of time it may well mean that readers do not understand exactly what you want so when bumping, best to try to add further details/examples etc.

 

[petrodude]

However, #12 of the Forum Rules says not to post duplicate questions so I have combined the two threads. That rule mentions "bumping" your thread which replying to it yourself does. If you have not received any replies after a period of time it may well mean that readers do not understand exactly what you want so when bumping, best to try to add further details/examples etc.

Thanks for explaining @Peter_SSs - I was unaware of this

 

[petrodude]

Almost 2 years back @Peter_SSs gave me fantastic solution in this thread. I think you are also trying to find something similar.

You must also check the solution @Alex Blakenburg gave. That is bit long but easy to understand.

Do check this thread if it helps you in some way. Else we are here to help you.

Referencing cell one above the current row in a structured table

Hi Experts, Got stuck up with a situation where I want to tell excel to add values in a Column from Header to the row one above current row. Unable to figure out how to reference a cell above in a structured table. The formula I'm currently using is -...

www.mrexcel.com

Hi @SanjayGMusafir

Thank you for your suggestion. It did teach me something new.

Hopefully you'll disagree with me, but I don't think this is the solution. This post from @Peter_SSs seems to be offsetting by one row, not querying columns for value - 1. What I'm seeing in that post is row-1, I think.

I look forward to your reply.

 

[SanjayGMusafir]

If I may explain

• The primary component for running total in a structured table is that it should calculate until the point I need/want excel to check up to.
• Rest what you are telling is part of sumifs function where you need to tell excel these are the criteria(s) to fulfil to sum the range of my choice.

The formula @Peter_SSs gave helps me choose the range of my choice and criteria(s) I have to define as per my need.

So think about that formula with modifications to your exact needs

Since you haven't shared any XL2BB so I can't share the exact modifications needed. It's always a challenge working on formulae with screenshots. Probably that is why you didn't get response from experts in the first attempt.

Hope it helps you in some useful way.

 

[petrodude]

If I may explain

• The primary component for running total in a structured table is that it should calculate until the point I need/want excel to check up to.
• Rest what you are telling is part of sumifs function where you need to tell excel these are the criteria(s) to fulfil to sum the range of my choice.

The formula @Peter_SSs gave helps me choose the range of my choice and criteria(s) I have to define as per my need.

So think about that formula with modifications to your exact needs

Since you haven't shared any XL2BB so I can't share the exact modifications needed. It's always a challenge working on formulae with screenshots. Probably that is why you didn't get response from experts in the first attempt.

Hope it helps you in some useful way.

@SanjayGMusafir Thank you for your help. I am having lots of trouble with XL2BB .. nothing I do allows me get around the "Microsoft excel security notice of disabling macros" .. is there another way to share / attach the .xlsx file?

 

[Peter_SSs]

nothing I do allows me get around the "Microsoft excel security notice of disabling macros" .

You probably didn't investigate the "Known XL2BB Issues" near the top of the instructions page. One of those would lead you to this page which most likely will solve that issue.