Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Sum Function (Worksheet)

In this post I’m going to show you how to use Sum function in excel worksheets.

Sum Function character

We use sum function to add values in Excel. Parameters for the sum function can be entered in few different ways. In this post I will show you these different methods one by one. Let’s consider this example sheet.

Sample data

So if we want to find total quantity using sum function, we can do it in few different ways. First method is we can directly enter the values inside sum function like this.

=SUM(9,10,4,6,3,8,1)

enter values directly in Sum function

Then we will get 41 as total.

Result

However if you use this method, total won’t change when you do changes to the quantities of the individual stones. So if you want to use sum function in such a way that total value changes when individual quantity of stones change, then you need to use cell references instead of direct values. Here is how you can do it.

=SUM(B2,B3,B4,B5,B6,B7,B8)

sum function with cell references

Now if you do any change to a individual quantity, it will be reflected in the total instantly. In above method we used individual cell references inside the Sum function. Next let's learn how to use ranges instead of individual cell references. You can simple replace cell references with the relevant range like this.

=SUM(B2:B8)

Sum function with range

In above example we used Sum function for contiguous range. So what if we want to use Sum function for non-contiguous range like this.

Non-contiguous range example data

I have highlighted the non-contiguous range in yellow. So now let’s learn how to use Sum function for this non-contiguous range. If we want to enter values directly then we can use Sum functions like we did earlier. So it will look like this.

=SUM(9,10,4,6,3,8,1,2,4,7,5)

And if we want to enter cell references then we can do it as follows.

=SUM(B2,B3,B4,B5,B6,B7,B8,E2,E3,E4,E5)

cell reference method in Sum function for non-contiguous range

And this is how you can enter range for Sum function for non-contiguous range.

=SUM(B2:B8,E2:E5)

Range input for Sum function for non-contiguous range

Contact Form

Name

Email *

Message *

Popular Posts