Let’s allow a few more rows for products in case we want to add some products later. Type Sum in A18. Then, select C18 and press Alt+equal sign.
Excel automatically finds the values to add together! Press Enter and do the same in F18 and G18. In H18 it doesn’t work – Excel adds F18 and G18 instead of the column. No problem: Just copy G18 (Ctrl+C) and paste it (Ctrl+V) in H18.
Select A18 to H18 and choose Top and Double Bottom Border.
3. Impress your colleagues with a dynamic Sum function
Let’s say we want to see the total sales for each category. It only takes a minute!
Write the name of the category in A21. In C21 we will use the SUMIF function:
a) Type =SUMIF( and select the range A4 to A17 with the mouse. Type a comma (or semicolon for European users)
b) The criteria is the product category in C21. Select that cell or type C21. Comma (or semicolon)
c) Choose the range from which you want the sum to be calculated, in this case C4 to C17. Close brackets.
Do this again in F21, G21 and H21.
The formula should look like this:
With some formatting, the report now looks like this:
To see the total sale figures for Bass guitars, type Bass guitar in A21, and the sums will change!
Bonus tip: Create a drop-down menu in A21. See how to do it in my Drop-down menu post.