Excel

[KandasD]

Hello

I have 3 tables: basic stock , issuing , procurement. I want make sure that the current stock is equal to the quantity issued by reference, subtracted from the quantity counted and added to the quantity procured. A bit like : Current stock = Quantity counted + quantity procured - quantity issued (NB: the calculation must be done even if the order of positioning of the references in the basic table is not respected in the 2 others)

Please help!!!

[#2]

@KandasD 

What you're asking would appear--and I emphasize the word "appear"--to be a very straightforward matter (as your "A bit like" sentence suggests). Yet it apparently is NOT straightforward, which is why you're asking for help.

It would be helpful to those of us desiring to help you if you would post your actual workbook--absent any confidential information--because otherwise all we can do is speculate and offer general and hypothetical suggestions. We'd need to see how these tables are laid out.

And could you inform us as to which version of Excel you're using...there are functions available in the most recent versions that are not available in older versions, functions that might be critical for your application.

[#3]

@mathetes 

Hello, thank you very much indeed for your reply. Herewith an explanation of what I mean. Hopefully it will make sense to you and guide you to help me.

[#4]

@KandasD 

Something like, Current stock:

= Initial_Quantity
  + SUMIFS(quantity_procured, date_procured,"<="&current_date)
  - SUMIFS( quantity_issued, date_issued, "<="&current_date)

[#5]

@KandasD 

It looks to me as if the reply you got from @Peter Bartholomew should work. Let us know.

[#6]

Thank you for your reply.
It works but only if I write the items in the order of the Warehouse table. How can I combine it with ''vlookup"? because items are not received or issued in order. Sometimes one article may stay untouched for days so I want it to search the issued or procured article and do the calculation.

[#7]

@KandasD 

Are you able to add criteria to match the product id to the SUMIFS function?

Are you able in your version of Excel to use the new FILTER function? Just try entering =FILTER and see if it accepts it. If so, it's a more powerful tool than VLOOKUP to match entries in your subordinate tables with the exact product...

[#8]

@mathetes 

Indeed it works. It just needs to search in the table the corresponding article and calculate no matter its position in the warehouse table.

[#9]

I will try

[#10]

I see "filter.xml" I don't know if this is it.

[#11]

@KandasD 

Here's a solution using FILTER, if your software works with it. I didn't yet incorporate the date, because we want first to see if it works at all.

=D5+SUM(FILTER($E$19:$E$22,$C$19:$C$22=$B5,0))-SUM(FILTER($E$12:$E$15,$C$12:$C$15=B5,0))

Copy this from E5 down the rest of the rows in your "Warehouse" table.

[#12]

@KandasD 

No. That means you would need to upgrade to the most current version. Is that possible? This video will introduce you to the capabilities.

https://www.youtube.com/watch?v=9I9DtFOVPIg

[#13]

Okay

[#14]

Is there a possibility for this to search and extract the reference and then calculate its quantity?

[#15]

@KandasD 

So far I have seen nothing to suggest it is important to search out quantities in any particular order.  Both the SUMIFS function and the more limited SUMIF that preceded it run through an entire column of data summing where a condition is met.

As far as VLOOKUP is concerned, there are other lookup functions that do twice as much, twice as well.

[#16]

@Peter Bartholomew 

One of the conditions, in addition to the date match, that your SUMIFS gets, is to match the product code as well, recognizing there can be multiple entries in the Procure or Distribute table for any given product. That's what @KandasD is looking for.

[#17]

Exactly

[#18]

the info of the product should all match as well

[#19]

@mathetes 

True.  For my own work, I have pensioned off both SUMIF AND COUNTIF on the grounds that they achieve nothing that cannot be performed with their '...IFS' equivalent, and so simply represent 'bloat'.

In the present case, I found the item references to match but no obvious date cut-offs.  Since I cannot remember when the '...IFS family came in, I decided not to risk SUMIFS when I only had a single-criterion (maybe, Office 2007?).