How to make many column formulas ignore the same lines in Excel?

30
2014-03
  • Jader Dias

    I have multiple columns and at the end of each one I have a SUM and MEAN lines. Now I want to ignore some lines thus making =SUM(A1:A15) into =SUM(A1:A13;A15) if I want to ignore line 14. Doing this for each formula is a tedious task.

    In my previous question I found out how to do it for a single column. I would place the A1:A15 string in a separate cell, then reference it using INDIRECT. This way I can alter a single cell and update every formula for that column.

    The problem is that I have multiple columns, with this approach I would have to update the B1:B15 cell for each column. How to simplify this update process?

    • Answers
  • Breakthrough

    You can use the OFFSET function to shift an entire range by a certain amount. For example, if you had the text ="A1:A15" in cell C1, you could get the range B1:B15 by using the following formula:

    =OFFSET(INDIRECT(C1), 0, 1)

    For your reference, the function is defined in Excel as =OFFSET(reference, rows, columns, [height], [width]). To retrain the width/height of the original range, do not specify the height or width arguments. Also, note that the rows and columns arguments can be positive or negative (so you can shift both up/down and left/right).

    Just another note, the OFFSET function works with entire ranges, so if in the previous example you entered the text ="A1:A12,A15" the returned range after using OFFSET to shift it right one column would be B2:B12,B15 as you would expect.


    • Related Question

    worksheet function - How to make an excel formula which totals several agecent rows based on cell values
  • Yishai

    I have an excel sheet with three columns: date, person and percentage. I would like to put in a data validation that flags cells if the total for a given data/person combination do not equal 100%. Is that possible?

    In other words, in the custom formula of a data validation, I would like to make the following type of formula.

     =if(sum( cells with a (date = the date on this row, person = person on this row))=1)

    Is there a function which will return the cells in a range conditioned on certain values, or will sum the cells.

    Note that if it is not possible to do two cells, I have no issue adding a cell which combines both values for the purpose of effecting the lookup.


    • Related Answers
  • 8088

    You want SUMIF (for a single criteria), the Microsoft SUMIF page on it, is pretty good. as is the OfficeArticle's SUMIF.

    Or just SUM with an array function (see above article from officearticles) for multiple criteria. For instance in the sheet below I entered the formula without curly braces into G2, and helt Ctrl+Shift+Enter, then I drug the formula down.

    an excel array formula

    If you have many many rows, then you'll need to move on to using DSUM for speed reasons. As you noted in your comment, they have to have their own little faux-table somewhere.

    Really it sounds like you're quickly moving past what Excel can do without busting into VBA. A database, or targeted application may be in your near future.

    edit: added information on array formula, pretty picture

  • RocketGoal

    I know that your question has already been answered, but I thought I might bring your attention to the SUMPRODUCT() function. It's like SUMIF, but I personally find it easier to work with and easier to use AND or OR nested within

    =Sumproduct((Range=Cell)*((Randge=ThisCell)+(Range=ThatCell))*(The figures to add up))

    Hope this helps.

    Mike