Skip navigation

excluding rows or columns to finish a deadline

316 Views 10 Replies Latest reply: Oct 31, 2013 4:53 PM by Barry RSS
hotwheels 22 Level 1 Level 1 (0 points)
Currently Being Moderated
Oct 31, 2013 10:29 AM

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)
  • Jerrold Green1 Level 7 Level 7 (28,200 points)
    Currently Being Moderated
    Oct 31, 2013 11:08 AM (in response to hotwheels 22)

    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

  • Jerrold Green1 Level 7 Level 7 (28,200 points)
    Currently Being Moderated
    Oct 31, 2013 12:25 PM (in response to hotwheels 22)

    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

  • papalapapp Level 1 Level 1 (80 points)
    Currently Being Moderated
    Oct 31, 2013 2:52 PM (in response to hotwheels 22)

    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.

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Oct 31, 2013 4:37 PM (in response to hotwheels 22)

    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

  • papalapapp Level 1 Level 1 (80 points)
    Currently Being Moderated
    Oct 31, 2013 4:37 PM (in response to hotwheels 22)

    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

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Oct 31, 2013 4:53 PM (in response to papalapapp)

    "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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.