Hi, newbie here. Thanks in advance for the help.
My question is in using matching or vlookup or hlookup? Am not so sure. I have two spreadsheet tabs. I want to fill up the "Current Stock Count" in first spreadsheet tab, where the product name and variation name will be searched in second spreadsheet, and add up quantity sold for that item, then the total quantity sold will be deducted from the Initial Inventory. How to formulate the code to get the result?
Again, thank you.
I think all you need in E3 is: =D3-SUMPRODUCT((B3='Tab 2'!B$3:B$10)*(C3='Tab 2'!C$3:C$10)*'Tab 2'!E$3:E$10)
While SUMPRODUCT is not wrong, it can become very inefficient if used on large quantities of data, try this instead...
=D3-SUMIFS('Tab 2'!$E$3:$E$10,'Tab 2'!$B$3:$B$10,'Tab 1'!B3,'Tab 2'!$C$3:$C$10,'Tab 1'!C3)
copied down
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you so much for both your help!
I just have a question though.. On code =D3-SUMPRODUCT((B3='Tab 2'!B$3:B$10)*(C3='Tab 2'!C$3:C$10)*'Tab 2'!E$3:E$10)
The entry which has no variation detail is returning a wrong number.. How to solve this? Thanks!
Did you try the SUMIFS version?
Hi. Yes. I used the SUMIF version. It's okay, I just put "none" for those items with no variation names. And it works fine.Thanks!
OK, happy to help
Hello again. Would like to ask for a quick help on how to formulate to return the cost? I have 2 tabs. Would like for it to check and match both product name and variation name. (even though cost is same)
firsts.png
seconds.png
Thank you so much.
Last edited by justgeminime; 11-03-2021 at 12:40 PM.
You should be able to use the exact same sumifS() function for that. Give it a try and let me know how you make out.
For this case, i didn't actually need any sum... so upon searching online for some formulas, i just used Index Match. and so far working okay.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks