Auto detect sum range when copying and pasting

{ 11 Comments }

Assume a simple two column data range as follows:

Product Amount
A 1
S 2
D 3
E 4
Product A
F 5
G 6
R 7
Product B
q 8
w 9
s 10
d 11
c 12
v 13
b 14
Product C

In the table above, one may want to compute the Product wise revenue.  Since the number of items falling in every product is different, one cannot copy and paste the SUM function from one product to another.  So one can adopt any one of the following two approaches:

1. Write the SUM function thrice; or
2. Use the short cut key for adding.  Here is the process (please note that this process will only work when there are no blank cells in the second column.  If there are blank cells, then the process mentioned below can be modified to still get the desired result):

a. Select range B2:B18
b. Press Ctrl+G > Special > Blanks > OK
c. Press Alt+= (this is the short cut key to generate the SUM function)

Now consider a different scenario.  Assume that the two column data range has the summarization row at the top of each Product block (rather than at the bottom as in the table above)

Product Amount
Product A
A 1
S 2
D 3
E 4
Product B
F 5
G 6
R 7
Product C
q 8
w 9
s 10
d 11
c 12
v 13
b 14

The second method described above will not work in this case because the Alt+= shortcut key only identifies thr range above or to the left.  To solve this problem, we need to write a formula for Product A which when copied down will auto adjust the range height.

You may refer to my solution in this workbook.  I have discussed two variants of the table above.

Leave a Comment

Your email address will not be published.

*

  • I am running a pre billing report, its sorted by customers, I am having the invoices from each customers of the last week for example, now after each customers invoices there is a empty row and than the new customers invoices are starting.

    I would like to set excel, at each empty row of that column L (lets say) should calculate the rows above it up till the previous empty row.

    Would you advise how to do it ?