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.

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.

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.

https://onedrive.live.com/redir?resid=4FB2415C3208A05E!105&authkey=!ALLpd7ptm7kHTBs&ithint=folder%2cxlsx

https://onedrive.live.com/redir?resid=4FB2415C3208A05E!105&authkey=!AN8e8-un83tL3yI&ithint=folder%2cxlsx

Hi,

There is no file there. Please check.

https://onedrive.live.com/redir?resid=8F1A5D4C686EF43E!106&authkey=!ALm1QDKo3ZU7nyA&ithint=file%2cxlsx

See above link. marked yellow the rows where i want it should calculate the totals above it

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?

https://onedrive.live.com/redir?resid=4FB2415C3208A05E!111&authkey=!AHMp2bJIt8r2Low&ithint=file%2cxlsx

Would appreciate your prompt reply

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.