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.

## Auto detect sum range when copying and pasting

{ 11 Comments }