Aug 05 2020 08:34 AM
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!!!
Aug 06 2020 11:41 AM
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.
Aug 07 2020 05:09 AM
Aug 07 2020 05:45 AM
Aug 07 2020 05:48 AM - edited Aug 07 2020 05:50 AM
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...
Aug 07 2020 06:05 AM
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.
Aug 07 2020 06:08 AM
No. That means you would need to upgrade to the most current version. Is that possible? This video will introduce you to the capabilities.
Aug 07 2020 07:11 AM
Aug 07 2020 08:44 AM
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.
Aug 07 2020 11:16 AM
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.
Aug 07 2020 12:52 PM
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?).