How to Summarize the counts based on 4 fields? Is xlookup or spill is better for drop down

In my count_Item sheet the sum of the counts in the In_item sheet are not grouping by the Lot_Number for some products. How can I fix this? For ex: F13 and F14 are showing duplicates. I understand the original author copied each line item from In-item into count_item for easy retrieval but the duplicates are annoying when I try to order the items.
Is this the correct way of summing? I want to count the products based on vendor + Description + Part_Number + Lot_Number

Also is there any easy way to add the new vendors and products to the drop-down lists? What is best for this kind- Xlookup, filter or spill?
Appreciate any suggestions to improve this sheet.

First of all, which Excel version do you have? Because if you use spill array and talks about XLOOKUP you don't have Excel 2013, and if you don't indicate the correct version of your Excel we will not be able to offer the best answer for you.

I added named tables for you and corrected some of your validations. See if it is ok for you and correct your Excel version.

Hi DJunqueira,
Thank you for your time. I'm using MS365, so can I use xlookup and spill array?

I'm not sure what you meant by "I added named tables "? Because I already have Name manger list for my drop down. Just want them to be dynamic to show the new values in the drop down instead of refreshing the Name manger range and without having extra empty cell references in each Name manager, because drop down shows lot of empty spaces. If you don't mind, can you please tell me what validations you fixed ?


Also, my real question for this post is not about drop down but to fix duplicates counts. How can I fix the counts in Count_items sheet based on columns vendor + Description + Part_Number + Lot_Number without duplicates

Glen, where should I update the products version in my profile or settings?
Thank you

Updated. Thank you.

Formulas for your 'COUNT_Item' sheet.

Formula:

Please Login or Register  to view this content.

True DJunqueira. I acquired this messy workbook and planning to maintain by fixing all the drop downs with filters, spill array and other functions.
First, I need to update my skills to O365 and fix these.

Dear DJ,
Thank you so much for all your time. Your quick solution shows your command on the product. Last night I spent couple of hours but could not update a single formula.

At first glance it looks like all the summary counts are working as desired. It will take a while for me to understand all your formulas.
I thought Unique function can only be used just for one column. I learned new thing here to expand it to more columns.
Also , what does T indicate in this formula? =SUMIF(Tin[Lot_Number],INDEX(A2#,,4),Tin[Quantity])
=F2#G2# -- #makes more sense for future product updates

Sorry for asking so many questions. Will update you once I add more columns and new drop down list for other products.

Thank you so much

Perfect solution

Tks for the feedback, glad to have helped.

As a good practice I always give a name for a table starting with a 'T'.

DJ, Can I ask one more question regarding table driven drop down list? Initially I will sort the table list, but when a new item is added to the list how can I make that table automatically sort?
For ex: to my table below if I add new value called Device, instead of manually sorting can I use =sort(indirect("Type"))? or any other way?

TYPE
Consumable
Equipment
Other
Reagent