# Sum column where column depth varies

1. ## 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  Register To Reply

2. ## Re: Sum column where column depth varies

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

Regards, TMS  Register To Reply

3. ## Re: Sum column where column depth varies

deleted, not appropriate.  Register To Reply

4. ## 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.  Register To Reply

5. ## Re: Sum column where column depth varies

Beyond me too New quick method:

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
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.  Register To Reply