Sum and conditional sum in Excel

We made a sales report in 5 minutes in a previous post, and we made it look great in 5 minutes in this post. To finish the report, let’s add the figures! Of course, it only takes 5 minutes!

1. Sum

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.

2. Format

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:

US:                        =SUMIF(A4:A17,A21,C4:C17)

European:          =SUMIF(A4:A17;A21;C4:C17)

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s