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

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

Regards, TMS

deleted, not appropriate.

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.