You will notice that the formulas that I am using are insanely long. I am looking to get them replaced with something more sane and effective. Right now they dont work (they did previously) hence the #N/A.
I need a formule in cell N2 of below sheet called 'Total' (below) that will see what the numbers in cells B2, D2, F2, H2, J2 & L2 (not all are neccessarily filled) refer to from sheet called 'Meal List' (hence why i used vlookup in my formuale) & adds just their corresponding blue variable (which is Cell C10 in the sheet called 'Meal List'). BUT I never want #N/A to show and sometimes I might have each cell B2, D2, F2, H2, J2 & L2 with a number and sometimes not all of them. You can tell from the long formuale i used that i used ISTEXT and ISBLANK a LOT of times.
So in Cell N2 (blue box) of below sheet 'total' I will have the total sum of all the blue variables of the each of numbers located in B2, D2, F2, H2, J2 & L2 (but somes 1+ might not have a number & i never want #N/A to show)
In case I still havent been understood, below I have 1 in cell B2, D2, F2, H2 & J2, let`s say 1 refers to Flashlight (from a table in another sheet) and the blue variable refers to their cost. In N2 I want the sum of all the cost of the flashlights. Sometimes i may have 2 flashlights (1 in B2 & D2), sometimes 3 etc....
I need a formule in cell N2 of below sheet called 'Total' (below) that will see what the numbers in cells B2, D2, F2, H2, J2 & L2 (not all are neccessarily filled) refer to from sheet called 'Meal List' (hence why i used vlookup in my formuale) & adds just their corresponding blue variable (which is Cell C10 in the sheet called 'Meal List'). BUT I never want #N/A to show and sometimes I might have each cell B2, D2, F2, H2, J2 & L2 with a number and sometimes not all of them. You can tell from the long formuale i used that i used ISTEXT and ISBLANK a LOT of times.
So in Cell N2 (blue box) of below sheet 'total' I will have the total sum of all the blue variables of the each of numbers located in B2, D2, F2, H2, J2 & L2 (but somes 1+ might not have a number & i never want #N/A to show)
In case I still havent been understood, below I have 1 in cell B2, D2, F2, H2 & J2, let`s say 1 refers to Flashlight (from a table in another sheet) and the blue variable refers to their cost. In N2 I want the sum of all the cost of the flashlights. Sometimes i may have 2 flashlights (1 in B2 & D2), sometimes 3 etc....
Cell Formulas | ||
---|---|---|
Range | Formula | |
N2 | N2 | =IFS(AND(ISTEXT(B2),ISTEXT(D2),ISTEXT(F2),ISTEXT(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,3,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,3,FALSE))+(VLOOKUP(F2,'Meal List'!$A$11:$AS$91,3,FALSE))+(VLOOKUP(H2,'Meal List'!$A$11:$AS$91,3,FALSE)),AND(ISTEXT(B2),ISTEXT(D2),ISTEXT(F2),ISBLANK(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,3,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,3,FALSE))+(VLOOKUP(F2,'Meal List'!$A$11:$AS$91,3,FALSE)),AND(ISTEXT(B2),ISTEXT(D2),ISBLANK(F2),ISBLANK(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,3,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,3,FALSE)),AND(ISTEXT(B2),ISBLANK(D2),ISBLANK(F2),ISBLANK(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,3,FALSE)),AND(ISTEXT(B2),ISTEXT(D2),ISTEXT(F2),ISTEXT(H2),ISTEXT(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,3,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,3,FALSE))+(VLOOKUP(F2,'Meal List'!$A$11:$AS$91,3,FALSE))+(VLOOKUP(H2,'Meal List'!$A$11:$AS$91,3,FALSE)+(VLOOKUP(J2,'Meal List'!$A$11:$AS$91,3,FALSE)))) |
O2 | O2 | =IFS(AND(ISTEXT(B2),ISTEXT(D2),ISTEXT(F2),ISTEXT(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(F2,'Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(H2,'Meal List'!$A$11:$AS$91,4,FALSE)),AND(ISTEXT(B2),ISTEXT(D2),ISTEXT(F2),ISBLANK(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(F2,'Meal List'!$A$11:$AS$91,4,FALSE)),AND(ISTEXT(B2),ISTEXT(D2),ISBLANK(F2),ISBLANK(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,4,FALSE)),AND(ISTEXT(B2),ISBLANK(D2),ISBLANK(F2),ISBLANK(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,4,FALSE)),AND(ISTEXT(B2),ISTEXT(D2),ISTEXT(F2),ISTEXT(H2),ISTEXT(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(F2,'Meal List'!$A$11:$AS$91,4,FALSE))+(VLOOKUP(H2,'Meal List'!$A$11:$AS$91,4,FALSE)+(VLOOKUP(J2,'Meal List'!$A$11:$AS$91,4,FALSE)))) |
P2 | P2 | =IFS(AND(ISTEXT(B2),ISTEXT(D2),ISTEXT(F2),ISTEXT(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,5,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,5,FALSE))+(VLOOKUP(F2,'Meal List'!$A$11:$AS$91,5,FALSE))+(VLOOKUP(H2,'Meal List'!$A$11:$AS$91,5,FALSE)),AND(ISTEXT(B2),ISTEXT(D2),ISTEXT(F2),ISBLANK(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,5,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,5,FALSE))+(VLOOKUP(F2,'Meal List'!$A$11:$AS$91,5,FALSE)),AND(ISTEXT(B2),ISTEXT(D2),ISBLANK(F2),ISBLANK(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,5,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,5,FALSE)),AND(ISTEXT(B2),ISBLANK(D2),ISBLANK(F2),ISBLANK(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,5,FALSE)),AND(ISTEXT(B2),ISTEXT(D2),ISTEXT(F2),ISTEXT(H2),ISTEXT(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,5,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,5,FALSE))+(VLOOKUP(F2,'Meal List'!$A$11:$AS$91,5,FALSE))+(VLOOKUP(H2,'Meal List'!$A$11:$AS$91,5,FALSE)+(VLOOKUP(J2,'Meal List'!$A$11:$AS$91,5,FALSE)))) |
Q2 | Q2 | =IFS(AND(ISTEXT(B2),ISTEXT(D2),ISTEXT(F2),ISTEXT(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,6,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,6,FALSE))+(VLOOKUP(F2,'Meal List'!$A$11:$AS$91,6,FALSE))+(VLOOKUP(H2,'Meal List'!$A$11:$AS$91,6,FALSE)),AND(ISTEXT(B2),ISTEXT(D2),ISTEXT(F2),ISBLANK(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,6,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,6,FALSE))+(VLOOKUP(F2,'Meal List'!$A$11:$AS$91,6,FALSE)),AND(ISTEXT(B2),ISTEXT(D2),ISBLANK(F2),ISBLANK(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,6,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,6,FALSE)),AND(ISTEXT(B2),ISBLANK(D2),ISBLANK(F2),ISBLANK(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,6,FALSE)),AND(ISTEXT(B2),ISTEXT(D2),ISTEXT(F2),ISTEXT(H2),ISTEXT(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,6,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,6,FALSE))+(VLOOKUP(F2,'Meal List'!$A$11:$AS$91,6,FALSE))+(VLOOKUP(H2,'Meal List'!$A$11:$AS$91,6,FALSE)+(VLOOKUP(J2,'Meal List'!$A$11:$AS$91,6,FALSE)))) |
S2 | S2 | =IFS(AND(ISTEXT(B2),ISTEXT(D2),ISTEXT(F2),ISTEXT(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,8,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,8,FALSE))+(VLOOKUP(F2,'Meal List'!$A$11:$AS$91,8,FALSE))+(VLOOKUP(H2,'Meal List'!$A$11:$AS$91,8,FALSE)),AND(ISTEXT(B2),ISTEXT(D2),ISTEXT(F2),ISBLANK(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,8,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,8,FALSE))+(VLOOKUP(F2,'Meal List'!$A$11:$AS$91,8,FALSE)),AND(ISTEXT(B2),ISTEXT(D2),ISBLANK(F2),ISBLANK(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,8,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,8,FALSE)),AND(ISTEXT(B2),ISBLANK(D2),ISBLANK(F2),ISBLANK(H2),ISBLANK(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,8,FALSE)),AND(ISTEXT(B2),ISTEXT(D2),ISTEXT(F2),ISTEXT(H2),ISTEXT(J2)),(VLOOKUP(B2,'Meal List'!$A$11:$AS$91,8,FALSE))+(VLOOKUP(D2,'Meal List'!$A$11:$AS$91,8,FALSE))+(VLOOKUP(F2,'Meal List'!$A$11:$AS$91,8,FALSE))+(VLOOKUP(H2,'Meal List'!$A$11:$AS$91,8,FALSE)+(VLOOKUP(J2,'Meal List'!$A$11:$AS$91,8,FALSE)))) |