Adding variable number of cells

mikiel111

New Member
Joined
Mar 17, 2020
Messages
38
Office Version
  1. 365
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....


My Stats.xlsm
BCDEFGHIJKLMNOPQRS
211111#N/A#N/A#N/A#N/A#N/A
Sheet1
Cell Formulas
RangeFormula
N2N2=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))))
O2O2=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))))
P2P2=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))))
Q2Q2=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))))
S2S2=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))))
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I added the -- right after SUMPRODUCT*. I`m not sure if those -- got it to work... or the fact that I was referrencing Sheet2 & my sheets are named differently ? but it works perfectly. Much appreciated!

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

I got a bit of brain damage when i wrote the below

=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))))"
 
Upvote 0
Glad you got it sorted & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top