Adding variable number of cells

M

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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
For N2 maybe
=IFERROR(SUM(IF(B2="",0,VLOOKUP(B2,'Meal list'!$A$11:$C$91,3,FALSE)),IF(D2="",0,VLOOKUP(D2,'Meal list'!$A$11:$C$91,3,FALSE)),IF(F2="",0,VLOOKUP(F2,'Meal list'!$A$11:$C$91,3,FALSE)),IF(H2="",0,VLOOKUP(H2,'Meal list'!$A$11:$C$91,3,FALSE)),IF(J2="",0,VLOOKUP(J2,'Meal list'!$A$11:$C$91,3,FALSE))),"")
 
Upvote 0
Cross posted Adding variable number of cells without #N/A ever showing

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
Depending on your data another possibility
=IFERROR(SUMPRODUCT(($B2:$L2='Meal list'!$A$11:$A$91)*('Meal list'!C$11:C$91)),"")
 
Upvote 0
You still need to tell us about it.
It is also a bit galling to see that you started a thread on another site, 4 hours after I posted a possible solution here and you hadn't even bothered to acknowledge my reply.
 
Upvote 0
I am sorry about this. I used to post on forums 24/7 back when i was a kid and cross posting was not a thing so I had no clue such a thing existed.

I am actually exploring both responses right now. In fact you will see that in the other thread I literally posted a couple of minutes before you posted your last message acknowledging that I am still exploring.

Immediately I`ll tell you they didnt work but my intention was to figure out how to use sumproduct & I thought I might try & figure it out how to fix it myself (now that I have knowledge of sumproduct which i didnt know about before) and report back instead of asking more. This is a simple issue & 99% I`m just not explaining myself well so maybe i`ll try one more time in hopes that I havent already 'ruined' myself.
 
Upvote 0
No , you haven't "ruined" yourself :)
I'll happily continue to help if needed.
 
Upvote 0
Thank you! I`m really sorry I started off on the wrong foot.


Let me try like this as I dont think sumproduct will work.. at least alone it wont work from what I`ve understood

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

adding.xlsx
ABCDEFGHIJKLMNOPQRST
1ContentsIDItem 1IDItem 2IDItem 3IDItem 4IDItem 5IDItem 6Total WeightTotal Costtotal BatteriesTotal Etc..
2Bag 11flashlight
3Bag 21flashlight2screws
4Bag 31flashlight3bolts1flashlight
5Bag 41flashlight5padding4drill1flashlight
6Bag 51flashlight1flashlight2screw5padding5padding
7Bag 61flashlight1flashlight2screw5padding5padding1flashlight
Sheet1


EquipmentWeightCostbatteriesetc..
1Flashlight10302
2screw120
3bolts1.520
4drill25505
5padding340
6nail120
 
Upvote 0
How about

+Fluff.xlsm
ABCDEFGHIJKLMNOPQ
1ContentsIDItem 1IDItem 2IDItem 3IDItem 4IDItem 5IDItem 6Total WeightTotal Costtotal Batteries
2Bag 11flashlight10302
3Bag 21flashlight2screws11322
4Bag 31flashlight3bolts1flashlight21.5624
5Bag 41flashlight5padding4drill1flashlight481149
6Bag 51flashlight1flashlight2screw5padding5padding27704
7Bag 61flashlight1flashlight2screw5padding5padding1flashlight371006
Sheet1
Cell Formulas
RangeFormula
O2:Q7O2=IFERROR(SUMPRODUCT(($C2:$M2=Sheet2!$A$10:$A$100)*(Sheet2!C$10:C$100)),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
I have formula in specific cell, i want to copy but dont want to be editable this cell
Replies
0
Views
194
Ibrarbutt
I
F
  • Question
How to add if input is empty jump it
2
Replies
11
Views
180
Fluff
Fluff
R
  • Question
Help to sum all cells above until blank cell
Replies
3
Views
156
RemyMoo
R
M
  • Question
Edit VBA to copy/paste cells to end
Replies
3
Views
325
Joe4
Joe4
HappyChappy
  • Question
Hours over basic
Replies
4
Views
160
Fluff
Fluff

Forum statistics

Threads
1,214,375
Messages
6,119,165
Members
448,870
Latest member
max_pedreira

Share this page

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