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.

• Reuven says:

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 ?

• Hi,

Try this

1. Suppose your data in column L is in range L2:L100
2. Select L2:L100 and press Ctrl+G > Special > Blanks > OK
3. Press =
4. Press the up arrow key once
5. Press Ctrl+Enter

Hope this helps.

• Reuven says:

Thanks for that, but with this it only calculates the 1 row above, i want it should calculate all the rows above until the previous empty row.

For example, i am having Totals in column K rows 20:29, than i am having a few empty rows, than i am having more totals rows 34:39, than again empty rows, and again totals rows 44:53

So what i want is, each empty row after a set of totals should calculate the rows above, in our case, Row 30 to calculate rows 20:29, row 40, to cal rows 34:39, and row 54 to calc. rows 44:53.

Would you appreciate your advise, thanks again for your help

• Hi,

Upload your workbook to OneDrive and share the link of the workbook here.

• Reuven says:
• Reuven says:
• Hi,

Running a formula here to get all your totals would be a challenge because of blank rows in between. You will be simplifying the problem to a great extent by removing all blank rows such as:

1. Delete the 12th row and type B in cell one cell below
2. Delete 19th and 20th rows and type C in one cell below and so on

In other words, the only blank row in your dataset should be where you want to generate the result.

Can you do this?

• Hi,

You first need to get rid of the \$ symbol and unrequired space/invisible characters from all cells. Then try this

1. Select range F17:K26 and press Ctrl+G > Special > Blanks > OK
2. Press Alt+=
3. Delete the formulas from J17,J22 and J26. Divide data in column K by column I and then drag down.