+ Reply to Thread
Results 1 to 5 of 5

Sum column where column depth varies

  1. #1
    Registered User
    Join Date
    09-22-2012
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    9

    Sum column where column depth varies

    Hi there

    Have sales data I want to sum, by Rep, by Store, not using the group function. The number of stores (rows) varies by Rep, so for Rep 1 who has 8 stores (held in cells D2 thru D9, D10 being blank/break before next reps data), the exact formula would be =sum(D2:D9) , this value being written into (say) E10. Then data for Rep 2 is held in D11 thru D40, exact formula =sum(D11:D40) written into E41. I am simplifying this greatly here obviously.

    I have hundreds of reps, all variable #'s of stores.

    I can filter the blank rows (10 and 41 in the above example), so want to write the same formula into Col E, that caters for the varying range to sum. It obviously needs to detect the blank row at the start of the range to set the start position and could end on next blank row/cell, or the row the formula was entered on. The data iteslf will never have blanks, though it may have zeroes. It is numeric data (sales).

    Thoughts?

    TIA.

    Ralph
    Last edited by rmg08057; 07-14-2015 at 08:52 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,322

    Re: Sum column where column depth varies

    Two thoughts: look into SUMIFS and COUNTIFS, etc; investigate Pivot Tables.

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,163

    Re: Sum column where column depth varies

    deleted, not appropriate.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Registered User
    Join Date
    09-22-2012
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Sum column where column depth varies

    Thanks guys.

    The SUMIFS was not flexible enough, while the d:d sums the whole range.

    Here is what I found works - it is beyond me somewhat:
    {=IF(ISERROR(MATCH(9.99E+307,IF($B$1:B9="",1,""))),SUM($B$2:B9),SUM(INDEX($B$2:B9,MATCH(9.99E+307,IF($B$2:B9="",1,""))):B9))}

    Thanks everyone.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,322

    Re: Sum column where column depth varies

    Beyond me too



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy Specific Rows with Column that Varies
    By Jarvin24 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2014, 02:05 AM
  2. Replies: 2
    Last Post: 10-18-2013, 11:18 AM
  3. Formula Help matchin colum a with colum b to display colum c
    By dbe82 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-08-2013, 10:11 PM
  4. Lookup OR Find value of Colum F in Column G and Provide output in Column H
    By Pankaj05 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-20-2013, 07:37 AM
  5. Lookup/Find value of Colum F in Column G and Provide output in Column H
    By Pankaj05 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-20-2013, 07:36 AM
  6. Set Print area when ending column varies
    By jbrown1968 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-24-2012, 03:32 PM
  7. Vlookup, using mid range and pulling back another column but the cell value varies
    By isatsam in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-20-2011, 05:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1