[Solved] Add items with respect to previous entries

[EduardDurb]

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

=(SUMIFS(C2,B2,"=1"))-(SUMIFS(D2,B2,"=1"))

[RusselB]

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.

[Villeroy]

Pivot_t98619.ods

(74.68 KiB) Downloaded 91 times

[MrProgrammer]

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

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 92 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

[EduardDurb]

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.

[Villeroy]

Pivot tables are very easy.
In this attachment "Sum - Diff" shows the difference based on the previous date.

[EduardDurb]

Thank you! You helped me get what I want! I appreciate the help, and all pfvyou have a nice day!

[Villeroy]

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)