Accumulative Sum for Same Product with Same Packing

[mmm1199]

Hi guys, does anyone know how to get the accumulative sum for same product type and name within a list?

Entry Date Product Type Product Name In Out Bal Qty Qty Qty 1/4/2018 AD-DS ADG8529 100 0 ? 2/4/2018 BO-KG SN500 100000 ? 3/4/2018 DS-KG Diesel 100 ? 4/4/2018 EO-KG SAE50 26000 ? 5/4/2018 BO-KG SN500 25000 ? 6/4/2018 DS-KG Diesel -50 ? 7/4/2018 DS-KG Diesel -50 ? 8/4/2018 EO-KG SAE50 -26000 ? 9/4/2018 AD-DS ADG8529 -25 ? 10/4/2018 AD-DS ADG8529 -25 ? 11/4/2018 AD-DS ADG8529 200 0 ? <colgroup><col><col><col><col><col span="2"></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

12/4/2018 BO-DS SN500 500 ?

 

[AhoyNC]

Is this what your looking for?

Copy formula in F3 down as needed.

Excel Workbook
	A	B	C	D	E	F
1	Entry Date	Product Type	Product Name	In	Out	Bal
2	Qty	Qty	Qty
3	1/4/2018	AD-DS	ADG8529	100	0	100
4	2/4/2018	BO-KG	SN500	100000		100000
5	3/4/2018	DS-KG	Diesel	100		100
6	4/4/2018	EO-KG	SAE50	26000		26000
7	5/4/2018	BO-KG	SN500	25000		125000
8	6/4/2018	DS-KG	Diesel		-50	50
9	7/4/2018	DS-KG	Diesel		-50	0
10	8/4/2018	EO-KG	SAE50		-26000	0
11	9/4/2018	AD-DS	ADG8529		-25	75
12	10/4/2018	AD-DS	ADG8529		-25	50
13	11/4/2018	AD-DS	ADG8529	200	0	250
Sheet

 

[mmm1199]

It works perfectly in my spreadsheet! Thanks a lot!

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.

 

[help needed yes please]

Hi there Ahoync, I am new here. How did you get the excel spreadsheet to show up. I have struggled to show mine. If you can help would be god. Thanks Frank

 

[jtakw]

Hi,

There are a few different ways, check here: https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[AhoyNC]

I use Excel Jeanie which you can download from here.
http://www.excel-jeanie-html.de/html/down_en.php

Along with jtakw link above take a look at post #13 in the attached.
[h=1]https://www.mrexcel.com/forum/about...ike-aladin.html?highlight=create+table+aladin[/h]

 

[help needed yes please]

Frank, Hi AhoyNC, great to have your help. I was interested in the formula you posted above 18/April: I made a new workbook page entered in exact data and the formula you gave. My results were different on the last two items F112 Shows -25 & F13 shows 200. All others were correct. Intrigued how most could be right yet some wrong. Trying to get a handle of how to understand your lovely formula. F

 

[help needed yes please]

frank to AhoyNC, tried to open downloaded excel Jeanie and it wont do it. gives message no can do. Any other ideas? Ta F

 

[AhoyNC]

Frank, I have also used HTML Maker which you can find at link in post #3 .
How to use HTML Maker
https://www.youtube.com/watch?v=-_QSkuZ0ppM

Not sure why you get different answers.
Your formula in F12 should be:

[TABLE="width: 619"]
<colgroup><col width="619"></colgroup>[TR]
   [TD="width: 619"]SUMIFS($D$3:D12,$B$3:B12,B12,$C$3:C12,C12)+SUMIFS($E$3:E12,$B$3:B12,B12,$C$3:C12,C12)[/TD]
 [/TR]
[/TABLE]

Formula in F13 should be:

[TABLE]
<colgroup><col width="619"></colgroup>[TR]
   [TD="width: 619"]SUMIFS($D$3:D13,$B$3:B13,B13,$C$3:C13,C13)+SUMIFS($E$3:E13,$B$3:B13,B13,$C$3:C13,C13)
[/TD]
 [/TR]
[/TABLE]

The formula works by using an expandable range.
You will notice that the first cell in each column is locked, so that the range expands as the formula is copied down. In the example below I have just used the first SUMIFS to add the values in the "IN" (column D). Hope that helps.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Entry Date	Product Type	Product Name	In	Out	Bal
2	Qty	Qty	Qty					Sum IN
3	1/4/2018	AD-DS	ADG8529	100	0	100		100
4	2/4/2018	BO-KG	SN500	100000		100000		100000
5	3/4/2018	DS-KG	Diesel	100		100		100
6	4/4/2018	EO-KG	SAE50	26000		26000		26000
7	5/4/2018	BO-KG	SN500	25000		125000		125000
8	6/4/2018	DS-KG	Diesel		-50	50		100
9	7/4/2018	DS-KG	Diesel		-50	0		100
10	8/4/2018	EO-KG	SAE50		-26000	0		26000
11	9/4/2018	AD-DS	ADG8529		-25	75		100
12	10/4/2018	AD-DS	ADG8529		-25	50		100
13	11/4/2018	AD-DS	ADG8529	200	0	250		300
Sheet