Automating a Cell Value Calculation Based on a Range

[Roux1]

Hi,

I’m trying to find a way to do this, but I can’t seem to find a sample anywhere yet.
Maybe I just don’t know the terms or the function to do it.

Ok, so I have this table

I want the Stock Quantity to be automaticaly updated.

I might have a bad approach to it, but i could only think about making a “Counter” range.

I’m trying to achieve it by doing something like this :

Set a variable lets say x = 0.

Then for each cell in A column which have “orange” value in it, it will look up the value in its representative D cell.

Then it will just sum the value like this x = x + D cell value.

Thus if the D cell value is +3, then x = 0 + (+3) which results in 3.

After that it look for the next cell in A column with “orange” value in it and found one with D cell value of -1.

Then the calculation starts again and update the last one as x = 3 + (-1) which results in 2.

Is it possible to do that ?

What should i do to achieve that ?

Or maybe there’s just another simple way around to achieve it that i couldn’t think of.

Any kind help would be appreciated, thank you.

[keme]

See the suggested solution attached.

As I see it, you don’t really need the counter column. I included it anyway, but empty in the attached file. If you need that value, Earnest Al’s suggested formula will do the job nicely.

Added data validation to “journaling” columns A and B. You can’t enter an item name before it is in place in the summary table.

It may be better to use a pivot table, but that will not auto-update. Editing item names, inserting rows or copy/paste from other spreadsheet cells can easily mess up references, formulas and validation. and so should be avoided. Protection may improve the behavior, but spreadsheets are generally not very good at preserving data integrity. Databases are made for that.

[Roux1]

Thank you very much for your reply keme !
This could be a great reference for me for later use.
And yes, Earnest’s formula is enough for me now, but yours is also a great learning sample for me.

Thank you so much !

[EarnestAl]

How about in cell G2 enter =SUMIF(A2:D6,"apple",D2:D6), in G3 =SUMIF(A2:D6,"orange",D2:D6),and G4 =SUMIF(A2:D6,"pear",D2:D6)

For the Counter coloumn you could enter in cell D2 =IF(B2="in",C2,0-C2) and drag/copy down.

It might be worth creating a named range for A2:D6 so that updating the extent of the named range would update the formulae. I don’t think that LO calc can do dynamic named ranges like Excel. Or just make it larger than needed to allow for future additions. Cheers, Al

[Roux1]

Thank you Earnest for showing me the way !
This simple way solves my problem.
God bless !

[newbie-02]

as mentioned in the other answer by @keme ‘sumproduct’, even in a simpler form like ‘=SUMPRODUCT(A$2:A$5=F2;D$2:D$5)’ would be my choice? care which references to let relative and which need ‘$-absolutity’ and you can copy one formula to a big range.

[newbie-02]

and ‘data - more filters - advanced filter’ with options ‘copy output to’ and ‘no duplicates’ could be a nice help to produce the ‘selection list’ in ‘big data’ situations …