Adding variable number of cells without #N/A ever showing

I have 2 sheets. in Sheet2 i have my list of items with associated variables (e.g. weight) and in sheet 1 I have a list of bags which contain the items. Any bag can have any amount of items up to 6. I want a formule that irrespective of the amount of items i have in the bag will add up the related variables. So in cell O6 of my workbook in sheet1 I want the the weights of all the items in it. So the weight of the 3 flashlights, 2 paddings and 1 screw. I want this to be done for each variable and importantly I want #N/A to never show.


Currently I`m using this but it is insane to arrange and fix (and I even broke it). I want something a bit less brain damaging

Please Login or Register  to view this content.

Oh. I used to post 24/7 on forums when i was a kid and this was never a thing. Sorry i didnt not know such a thing exists and is frowned upon. I am reading the links you sent me and i`ll post a link to the thread. Again, sorry

Some posts where made and I am still exploring them

https://www.mrexcel.com/board/thread...cells.1130450/

The solutions suggested have not worked and I have posted this to further clarify what I require

In sheet 1 (top table) I have bags and in each bag I can have up to 6 items. Each item has a weight, cost etc... & these are found in Sheet 2 with their corresponding ID. I will manually put in the items` ID in sheet 1 in the columns ID. Via index/match the corresponding item name will load adjacent to them (under column Item *number*). In the coloured columns (e.g. Total weight) I need the sum of that variable for the items that are in that particular bag.

So for example bag number 3 has a flashlight, bolts and another flashlight. I need the sum of the the weight of the flashlight, bolts and flashlight in the total weight column for that corresponding bag. I also need #N/A to never show. (so if no bags, no #N/A showing) Simple right? But I may have had 3, 4 or etc.. items in the bag & I will have 100s if not 1000s of bags so no manual calc can be done.

PS the bottom table starts with column A,B,C etc... but has the title row in row number 9, so the first item (flashlight) starts in row 10

I have also re-uploaded the workbook with some formatted cells to make referening to stuff easier

From the other forum this has solved my issue

=IFERROR(SUMPRODUCT( -- ($C2:$M2='Meal List'!$A$10:$A$100)*('Meal List'!C$10:C$100)),"")