locked
Excel addition across worksheets with exclusion of a row based on string RRS feed

  • Question

  • I Need to be able to add some values which a located on Col E in 100s of sheets within the same workbook (excel file)

    Here is what I did I inserted a worksheet at the begining of these worksheets and called it first then placed another at the end and called it Last I then placed a worksheet to the left of First which makes it the very first I call this sheet the summary sheet and then in a cell on the Summary sheet I put in the following formula;

    =SUM(First:Last!E2:E100) - Basically sum everythin in cells E1 to E100 on ALL worksheets from First to Last

    It works fine except I have a row in these 100s of worksheets which has the word Total and it has the sum of the E col for each Sheet. I would have love to simply sum up the total value from each sheet but the trouble is Total does not shouw up in teh same cell number across the sheet so in some sheets it may be on E15 in other E20 or E10 and so on.

    The word Total is always in Col A and the value in Col E so my formula above actually get the values in E including the total for that sheet so doubling the figures for each sheet

    I need a way to

    Get the sum of all totals in my sumary sheet or

    Sum up everything in col E excluding the Row that has "Total" in col A or

    Better yet between the last entry and the row that has total on every sheet thier are some blank cells in Col E before the value for total if the fomula can simply add things up and once it hits a blank cell to simply assume addition over.

     

    • Moved by Marvin_Guo Friday, January 10, 2014 1:36 AM
    Wednesday, January 1, 2014 3:09 AM

Answers

  • Hi kd01,

    I am afraid that this forum is not a correct forum for this issue. Since this forum is to discuss issues about Excel developement.In my opinion, it is an issue regarding to excel formula .

    The Excel IT Pro Discussions forum will be a better forums for you to ask this question.

    Have a nice time!

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by Just Karl Friday, January 10, 2014 4:54 PM
    • Marked as answer by Just Karl Sunday, January 26, 2014 2:15 AM
    Wednesday, January 8, 2014 8:01 AM

All replies

  • You could use

    =SUM(First:Last!E2:E100)/2


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 1, 2014 12:05 PM
  • Hi kd01,

    I am afraid that this forum is not a correct forum for this issue. Since this forum is to discuss issues about Excel developement.In my opinion, it is an issue regarding to excel formula .

    The Excel IT Pro Discussions forum will be a better forums for you to ask this question.

    Have a nice time!

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by Just Karl Friday, January 10, 2014 4:54 PM
    • Marked as answer by Just Karl Sunday, January 26, 2014 2:15 AM
    Wednesday, January 8, 2014 8:01 AM