## [Solved] Add items with respect to previous entries

### [Solved] Add items with respect to previous entries

Hello everyone,

This is my very first post here. I am currently working on a calc spreadsheet for a personal inventory.

During this process, I think I have to use the SUMIF function, and I managed to do so. However, I cannot get the SUMIF function to sum up values with respect to their corresponding cell.

Data Batch KG in KG out Total (KG)
12.06.2019 1 10 0 10
13.06.2019 2 30 0 0 10
14.06.2019 1 20 0 20
15.06.2019 1 10 0 10
16.06.2019 2 15 0 0

I need to add a specific amount (KG) of several batches of products. The problem is that the batches are not one under another, and they may alternate. How do I make the function to add each KG of each batch separately into a daily total?

So, on the 14th of June 2019, I want to automatically obtain 30, meaning also the 12.06.2019 with respect to Batch number 1, and on the 15 of June I want to obtain 40. The same applies to batch 2 on 13.06 and 16.06 (45).

This is the SUMIFS I used
Code: Select all   Expand viewCollapse view
=(SUMIFS(C2,B2,"=1"))-(SUMIFS(D2,B2,"=1"))
Attachments
Inventory.ods
Last edited by EduardDurb on Wed Jul 10, 2019 3:30 pm, edited 1 time in total.
OpenOffice 4.1.6 on Windows 10
EduardDurb

Posts: 3
Joined: Tue Jul 09, 2019 5:23 pm

### Re: Adding inventory items with respect to previous entries

The parameter layout is different for SUMIFS vs. SUMIF, but it looks like you are close to having the correct layout.
The problems I'm seeing with your code, is that you have a single cell for the first and second parameters, when the funchon calls for a range.
Since you only have one conditional parameter in each of those, you could use the SUMIF function, rather than SUMIFS.
Your current parameter layout is correct for the SUMIFS function, so to change it to the SUMIF function, you would need to reorganize your parameters.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5794
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Adding inventory items with respect to previous entries

Pivot_t98619.ods
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 27570
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Adding inventory items with respect to previous entries

Hi, and welcome to the forum. Thank you for the attachment.

EduardDurb wrote:How do I make the function to add each KG of each batch separately into a daily total?
If you want daily summaries, use the SUMPRODUCT function.
If you want a single inventory summary for the batches, use a Pivot Table (formerly called Data Pilot). After adding new data, update the table with Right-click → Refresh.
201907091349.ods

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3941
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: Adding inventory items with respect to previous entries

Thank you all for the quick and insightful replies. I think that I didn't explain correctly what I wanted to do.

I have attached the model that I want to reach. In my mind, it sounds pretty simple, but it looks like it is not. I want to make each row independent. According to the batch number I have, I want the program to add/decrease the introduced amount (from the introduced batch number) and show the -CURRENT- total. Ideally it would let me extend it downwards indefinitely.

Please, if you are kind, check the attachment.
Attachments
Inventory.ods
OpenOffice 4.1.6 on Windows 10
EduardDurb

Posts: 3
Joined: Tue Jul 09, 2019 5:23 pm

### Re: Adding inventory items with respect to previous entries

Pivot tables are very easy.
In this attachment "Sum - Diff" shows the difference based on the previous date.
Attachments
Pivot_t98619_2.ods
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 27570
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Adding inventory items with respect to previous entries

Thank you! You helped me get what I want! I appreciate the help, and all pfvyou have a nice day!
OpenOffice 4.1.6 on Windows 10
EduardDurb

Posts: 3
Joined: Tue Jul 09, 2019 5:23 pm

### Re: [SOLVED]Addinnventory items with respect to previous ent

Sorry, I forgot to describe how to actually get a running total in a pivot table:
Right-click pivot > Edit Layout...
1) Double-click the data field in question ("Sum - Diff" in this example)
2) In the following smaller dialog click [More Options]
3) Type: Running Total
Base field: Date (in this example)
Attachments
How to get the running total of a pivot table's data field.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 27570
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany