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

=IFS(AND(ISTEXT(C2),ISTEXT(E2),ISTEXT(G2),ISTEXT(I2),ISBLANK(K2)),(VLOOKUP(C2,'[My Stats.xlsm]Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(E2,'[My Stats.xlsm]Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(G2,'[My Stats.xlsm]Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(I2,'[My Stats.xlsm]Meal List'!$A$11:$AS$91,4,FALSE)),AND(ISTEXT(C2),ISTEXT(E2),ISTEXT(G2),ISBLANK(I2),ISBLANK(K2)),(VLOOKUP(C2,'[My Stats.xlsm]Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(E2,'[My Stats.xlsm]Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(G2,'[My Stats.xlsm]Meal List'!$A$11:$AS$91,4,FALSE)),AND(ISTEXT(C2),ISTEXT(E2),ISBLANK(G2),ISBLANK(I2),ISBLANK(K2)),(VLOOKUP(C2,'[My Stats.xlsm]Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(E2,'[My Stats.xlsm]Meal List'!$A$11:$AS$91,4,FALSE)),AND(ISTEXT(C2),ISBLANK(E2),ISBLANK(G2),ISBLANK(I2),ISBLANK(K2)),(VLOOKUP(C2,'[My Stats.xlsm]Meal List'!$A$11:$AS$91,4,FALSE)),AND(ISTEXT(C2),ISTEXT(E2),ISTEXT(G2),ISTEXT(I2),ISTEXT(K2)),(VLOOKUP(C2,'[My Stats.xlsm]Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(E2,'[My Stats.xlsm]Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(G2,'[My Stats.xlsm]Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(I2,'[My Stats.xlsm]Meal List'!$A$11:$AS$91,4,FALSE)+(VLOOKUP(K2,'[My Stats.xlsm]Meal List'!$A$11:$AS$91,4,FALSE))))