[Solved] Add items with respect to previous entries

Discuss the spreadsheet application

[Solved] Add items with respect to previous entries

Postby EduardDurb » Tue Jul 09, 2019 5:39 pm

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
(9.73 KiB) Downloaded 9 times
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

Postby RusselB » Tue Jul 09, 2019 7:31 pm

Can't download your attached spreadsheet right now, but you are using SUMIFS rather than SUMIF.
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.6 and LibreOffice 6.0.6.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.
User avatar
RusselB
Moderator
 
Posts: 5300
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Adding inventory items with respect to previous entries

Postby Villeroy » Tue Jul 09, 2019 7:43 pm

Pivot_t98619.ods
(74.68 KiB) Downloaded 10 times
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
User avatar
Villeroy
Volunteer
 
Posts: 26980
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Adding inventory items with respect to previous entries

Postby MrProgrammer » Tue Jul 09, 2019 9:11 pm

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
(17.02 KiB) Downloaded 6 times

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.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3785
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Adding inventory items with respect to previous entries

Postby EduardDurb » Tue Jul 09, 2019 10:37 pm

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
(3.73 KiB) Downloaded 7 times
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

Postby Villeroy » Wed Jul 10, 2019 12:05 am

Pivot tables are very easy.
In this attachment "Sum - Diff" shows the difference based on the previous date.
Attachments
Pivot_t98619_2.ods
(62.56 KiB) Downloaded 7 times
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
User avatar
Villeroy
Volunteer
 
Posts: 26980
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Adding inventory items with respect to previous entries

Postby EduardDurb » Wed Jul 10, 2019 3:30 pm

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

Postby Villeroy » Wed Jul 10, 2019 4:24 pm

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
Pivot_Running_Total.png
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
User avatar
Villeroy
Volunteer
 
Posts: 26980
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 17 guests