excluding rows or columns to finish a deadline

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

Like (0)


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

Like (0)


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...?

Like (0)


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

Like (0)


To be more flexible, you could use SUMIF. Add a row tohave extra cells for applying a marker, e.g. X. Now you can sumif 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.

Like (0)


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.

Like (0)


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

SUMIFSThe SUMIFS function returns the sum of the cells in a collection where the test values meet the given conditions.
 SUMIFS(sumvalues, testvalues, condition, testvalues…, condition…)
 sumvalues: A collection containing the values to be summed. sumvalues is a collection containing number, date/time, or duration values.
 testvalues: A collection containing values to be tested. testvalues 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 testvalues can be expressed as a Boolean value of TRUE or FALSE.
 testvalues…: Optionally include one or more additional collections containing values to be tested. Each testvalues collection must be followed immediately with a condition expression. This pattern of testvalues, condition can be repeated as many times as needed.
 condition…: If an optional collection of testvalues is included, an expression that results in a logical TRUE or FALSE. There must be one condition following each testvalues collection; therefore, this function will always have an odd number of arguments.
 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 sumvalues is included in the sum.
 All arrays must be of the same size.

Like (0)
 SUMIFS(sumvalues, testvalues, condition, testvalues…, condition…)

HI HW,
Markers in row 2 tell Numbers which columns to exclude from the Sum in column P.
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

Like (0)


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.

Like (0)


"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

Like (0)
