I need help with a formula or macro that will allow me to sum a column, based on matching the data in one column from data in another column, without using criteria based on a single cell... only criteria ranges.
Scenario:
When my crew go to cycle count our inventory, we come across wrapped pallets of completed kits. But I need to be able to ID the makeup of the completed kit by 3 different smaller kits, without tearing the wrap off of the pallets and counting them individually and then re-wrapping the pallet. The only other way to identify what’s in each pallet is to run a system generated report that breaks it all down based on the Pallet ID, then match it against the Pallet ID’s that are identified during the counts. I could do all of this manually, but it would take forever. I need something that will automate the totals for me.
So you have:
Column 1 (column to sum) = Quantity of each Kit Type (Kit A, Kit B, Kit C) (From the Report Pivot Table)
Column 2 = List of all Pallet ID's (from the Report Pivot Table)
Column 3 = The Identified Pallet ID during the cycle counts (Location Tabs)
I've tried various V-Lookups, Index/Match, If statements, combinations of all of these, and I find myself unable to figure this one out. If I can get some help on this, I would greatly appreciate it!!!
(see attachment for example spreadsheet)
Respectfully,
Dave
Hello,
Not sure why you separate the data as in your example, just makes things more difficult than needed.
Attached file shows one way to go about, I am using a helper column to bring in some needed data to your table. With the help of the extra column it is just an ordinary INDEX & MATCH lookup to find you want.
Hope it helps you forward in your work.
Br,
Anders
Hello,
Had some spare time which I spent creating an example of using Pivot Tables instead.
Perhaps not fancier, but a lot more easier and gives you a dynamic update on new/altered data, just update the Pivot Table.
Br,
Anders
Thank you Anders... the layout is setup this way as part of a larger worksheet that automates all of my data from inventory cycle counts. I'm trying to create formulas that will automate/auto populate with just the refresh of specific pivot tables, in this case to match the kit that was counted to which pallet ID, so that I can determine the breakdown (from the pivot table) that will ultimately feed my summary tab.
I will give your examples a try and see how I can incorporate them with my summary tab.
Thank you so much!!
Dave
