+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    01-06-2019
    Location
    Euro
    MS-Off Ver
    365
    Posts
    32

    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.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,901

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

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

  3. #3
    Registered User
    Join Date
    01-06-2019
    Location
    Euro
    MS-Off Ver
    365
    Posts
    32

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

    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

  4. #4
    Registered User
    Join Date
    01-06-2019
    Location
    Euro
    MS-Off Ver
    365
    Posts
    32

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

    Some posts where made and I am still exploring them

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

  5. #5
    Registered User
    Join Date
    01-06-2019
    Location
    Euro
    MS-Off Ver
    365
    Posts
    32

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

    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
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-06-2019
    Location
    Euro
    MS-Off Ver
    365
    Posts
    32

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

    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)),"")

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Adding data from variable cells
    By bjoanmark in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-28-2019, 06:35 AM
  2. Replies: 3
    Last Post: 02-17-2015, 11:53 PM
  3. [SOLVED] Calculate average of variable number of cells variable number of times
    By WeirnetherlandsBart in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2014, 10:11 AM
  4. adding variable sums from different cells
    By ioneabee in forum Excel General
    Replies: 5
    Last Post: 05-13-2010, 04:09 AM
  5. Adding variable number of blanks
    By ringnab in forum Excel General
    Replies: 2
    Last Post: 07-12-2006, 04:29 PM
  6. [SOLVED] count number of cells in range showing between 320 and 345
    By annieandtika in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-24-2006, 09:43 PM
  7. adding variable number of columns
    By confused in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-23-2005, 06:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1