10 Replies Latest reply: Oct 31, 2013 4:53 PM by Barry
hotwheels 22 Level 1 Level 1 (0 points)

hi all. i have been working on a very involved spreadsheet in numbers and have been receiving some absolutely mission critical help from posters on the forum kind enough to answer my questions (thank you!).

 

i am really desperate to finish this spreadsheet as i am way past my deadline for this and am finding the need to triage without losing too much work. some help please if possible:

 

1. is it possible to /exclude/ a COLUMN of cells from a SUM calculation that i am totalling up on the right, without deleting the whole ROW? what i mean is that i have set up a calculation in each ROW to add a percentage to from another COLUMN that is independent from this SUM but i want to simply remove this COLUMN with the added percentage from the horizontal SUM.

A. Can I MOVE this column off to the right and have it excluded from the SUM or will it still be included in the SUM since it was included originally?

B. is there a way to simply turn /off/ the COLUMN in a manner that will exclude it? i tried hiding it but even if it is hidden i am seeing that it is still included in the calculation...

 

2. how does the SUM function work in terms of /adding/ ROWS/COLUMNS? if i add a row or column between rows or columns that are being summed it seems that these will get added to the cells being summed. however, is it the case that if i add this row or column in /front/ of these cells or at the end of these cells (say just in front of the sum cell) that these are not included?

 

i realize i can test this but i am up against a deadline and getting very close to finishing so any pro-level help on this kind of macro explanation would be really welcome.

 

THANKS


MacBook Pro, OS X Mavericks (10.9)
  • 1. Re: excluding rows or columns to finish a deadline
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    HW,

     

    SUM can accept multiple ranges. Let's say that you want to sum every column from A to M, except for column D. You can write:

     

    =SUM(A:C, E:M)

     

    Jerry

  • 2. Re: excluding rows or columns to finish a deadline
    hotwheels 22 Level 1 Level 1 (0 points)

    ok. i get that. (took me a couple of looks...)

     

    is there a way to sum A:M and tell it to /exclude/ D?

     

    i realize this sounds like the proverbial one half dozen but for some reason i am thinking this may be important for me to know at some point.

     

    sure would be great if there was an "Off" option for a column instead of Hide which simply keeps it in the equation but hides it from view. ideally i would like to /see/ this column but have it exclude itself from the SUM.

     

    i mean, i think part of my problem is that i have 18 sections and i total up the Columns in each section so that changing the equation of the ROWS means that i have to actual delete the Column (AFAIK) or i have to actually go in and change all 800 or so SUM equations (which are divided up into 18 sections) at the right or each Row. so this would be a very manual process...

     

    if that makes sense

  • 3. Re: excluding rows or columns to finish a deadline
    hotwheels 22 Level 1 Level 1 (0 points)

    hi jerrold.

     

    thanks. sorry for the multiple posts as i try and get this done. instead of starting a new post can i just also ask you if there is a way to MOVE a Column INBETWEEN cells that are being summed on the right in a way that does not involve this set of Cells in the column being picked up in the SUM on the right? i have a cell that is being  used which is based on a PRODUCT of another Column and i would like to move this column just to the left of this result but if i move it over there it is being picked up in the existing SUM on the right.

     

    if i just have it located at another place in the spreadsheet it is going to look weird and if i can put it over there it will help me keep track of things.

     

    does /anything/ that is placed between cells get picked up in the SUM irrespective of settings leaving me with the option only of putting it all the way at the beginning or manually changing the sum to EXCLUDE this newly located Column...?

  • 4. Re: excluding rows or columns to finish a deadline
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    By the way, referring to my last post's expression, =SUM(A:C, E:M) will sum every row in all those columns. If you want to restrict the row, you need to specify it.

     

    Changing things around after all the expressions are written is never easy.

     

    Jerry

  • 5. Re: excluding rows or columns to finish a deadline
    papalapapp Level 1 Level 1 (80 points)

    To be more flexible, you could use SUMIF. Add a row tohave extra cells for applying a marker, e.g. X. Now you can sum-if A:M for all columns with empty marker cells. If you type something into the marker cell, it won't be part of the sum and you can hide the column.

  • 6. Re: excluding rows or columns to finish a deadline
    hotwheels 22 Level 1 Level 1 (0 points)

    hi jerry.

     

    yeah, somehow i want to be able to move a column in a manner that doesn't affect the previous work i have done. from what i am finding if i move a cell to the /beginning/ of a set of cells in a Row which have been added, this new Row of cells /also/ gets added to the previously set up SUM.

     

    this is not what i would have expected and it seems hard for me to imagine that there isn't a way to insert the new Row in a way that doesn't affect the existing SUM. i mean, seems OK to have a Row put in the MIDDLE of this SUM default to being added but to put it at the beginning and have it automatically added seems very hard to work around unless one had an option not to add it.

     

    presumably i could add a very small BLANK Row and this would terminate the SUM so that i could add the row in front? anyway, seems like i should have an option on whether to include it or an off switch or a way to make a divsion between a set of Rows or Columns are hard equation termination line.

     

    anyway, things are moving in no small part to you help so THANK YOU.

  • 7. Re: excluding rows or columns to finish a deadline
    hotwheels 22 Level 1 Level 1 (0 points)

    hi P.

    can you help me more here? i have a hard time with the terminology even though i am decent with logic and with math. what is an empty marker cell? can you explan verbally what sum if will do or should do? the help menu on this seems a little lacking from an english explanation perspective.

    THANKS

    -


    SUMIFS

     

     

    The SUMIFS function returns the sum of the cells in a collection where the test values meet the given conditions.

    • SUMIFS(sum-values, test-values, condition, test-values…, condition…)
      • sum-values:  A collection containing the values to be summed. sum-values is a collection containing number, date/time, or duration values.
      • test-values:  A collection containing values to be tested. test-values is a collection containing any type of value.
      • condition:  An expression that results in a logical TRUE or FALSE. condition is an expression that can contain anything as long as the result from comparing condition to a value in test-values can be expressed as a Boolean value of TRUE or FALSE.
      • test-values…:  Optionally include one or more additional collections containing values to be tested. Each test-values collection must be followed immediately with a condition expression. This pattern of test-values, condition can be repeated as many times as needed.
      • condition…:  If an optional collection of test-values is included, an expression that results in a logical TRUE or FALSE. There must be one condition following each test-values collection; therefore, this function will always have an odd number of arguments.
    Usage Notes
    • For each of the test and condition value pairs, the corresponding (same position within range or array) cell or value is compared to the condition. If all of the conditions are met, the corresponding cell or value in sum-values is included in the sum.
    • All arrays must be of the same size.

     

  • 8. Re: excluding rows or columns to finish a deadline
    Barry Level 7 Level 7 (29,180 points)

    HI HW,

     

    Markers in row 2 tell Numbers which columns to exclude from the Sum in column P.

    Screen Shot 2013-10-31 at 4.32.36 PM.png

    Numbers not equal to 2 have been used in the marked columns to make it obvious that they have not been included in the sum. The actual number in these columns has no significance other than that.

     

    The marker in cells in row 2 can be any text or number. The test is only that there is nothing entered in the cell.

     

    Regards,

    Barry

  • 9. Re: excluding rows or columns to finish a deadline
    papalapapp Level 1 Level 1 (80 points)

    Ok I'll have to translate the formula here since I'm on a different language, maybe Jerry could assist.

     

    Insert a row anywhere you like.

     

    As the formula, instead of calculating =SUM(C4:J4) use this: =SUMIF(C2:J2,"",C4:J4). This sums row 4 from C to J for all columns that are empty in row 2.

     

    If you type anything in row 2, that column won't be part of the sum because it isn't empty anymore. Only columns with empty cells in row 2 will be included in the sum.

     

    Notice that SUMIF in this example is 34 but not 36 since it does not include column D.

     

     

    sumif.png

     

    sumif2.png

  • 10. Re: excluding rows or columns to finish a deadline
    Barry Level 7 Level 7 (29,180 points)

    "Ok I'll have to translate the formula here since I'm on a different language, maybe Jerry could assist."

     

    Looks very clear to me, papalpapp.

     

    Regards,

    Barry