Using SUMIFS combined with Vlookup - HELP!

Thanks, Alan.

Just posted the sheet.

This, of course, is a very simplified version but once the formula works it'll apply directly over to the main document.

Once again, any help is tremendously appreciated.

Done - wasn't sure what to put haha

I think (suspect) you need an "opening" stock for each product for each shop. Then use SUMIFS to calculate the stock movements.

So "FROM" is a negative SUMIFS, "TO" a positive SUMIFS: so for any Shop/Product combination you have Current stock balance = Opening Balance + "To" SUMIFS -"From" SUMIFS

Have I understood correctly and does the above fit with your requirement ?

Thanks, John.

If it isn't too much hassle would there be any chance you could provide an example formula to the above-attached sheet - I've spent hours writing formulas and I'm getting absolutely nowhere haha. - it's one of those days.

Does that formula also include the buy/sell/transfer part?

Any further help is super appreciated.

Correction to H6

=SUMIFS($D$18:$D$23,$B$18:$B$23,"Sell",$C$18:$C$23,$A6)-SUMIFS($D$18:$D$23,$B$18:$B$23,"Buy",$C$18:$C$23,$A6)

You, my friend - are quite possibly the biggest lifesaver. This helps more than you can imagine! Thanks so much.

Absolute legend!

Works a dream!

You're vey welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Shortened formula in C6

Formula:

Please Login or Register  to view this content.

Thanks, John.

One final question (apologies) - the columns B6:B10 are meant to show the total amount of stock we have. Currently when doing a transaction, say Shop1 transfers 10 apples to Shop2, it shows Shop1 as being -10 and shop2 +10, meaning that the total stock summary has 0 apples rather than 10. What should I add to the formula so that if a number is negative it counts as 0.
Assume it's an if statement - if you please help that'd be tremendous.

Your help so far has been invaluable.

Thanks again.

Try

=SUMIF(C6:BB6,">0")

In your shortened formula of C6 - This: =SUM(SUMIFS($D$18:$D$23,$C$18:$C$23,$A6,$F$18:$F$23,C$5,$B$18:$B$23,{"Transfer","Buy"}))-SUM(SUMIFS($D$18:$D$23,$C$18:$C$23,$A6,$E$18:$E$23,C$5,$B$18:$B$23,{"Transfer","Buy"}))

Should it be "Sell" in the final part - {"Transfer","Sell"} ?

YES!! Sorry!