Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to calculate four ways of Calculating total using Formulas in Excel. Let’s get started.
Sample Data
The screenshot below shows the sales sample data. The data is already formatted as an Excel Table using CTRL + T
PRODUCT Function
The PRODUCT function multiplies all the numbers given as arguments and returns the product. The first solution we want to consider is how to use the Product Function to calculate the Total in column F.
- In cell F2, execute the formula: =PRODUCT([@Price],[@Quantity])
- Click Enter
The formula automatically fill down the result without the need for dragging down using the fill handle. This is because the dataset is stored as an Excel Tables. See the result below:
Multiplication Sign (*)
The second solution we want to consider is how to use the multiplication sign (*) to calculate the Total in column G
- In cell F2, execute the formula: =[@Price]*[@Quantity]
- Click Enter
The formula automatically fill down the result. See the result below:
SUMPRODUCT Function
The third solution we want to consider is how to use the SUMPRODUCT Function to calculate the Total in column H. The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays. The default operation is multiplication, but addition, subtraction, and division are also possible.
- In cell H2, execute the formula: =SUMPRODUCT([@Quantity],[@Price])
- Click Enter
The formula automatically fill down the result. See the result below:
SUMPRODUCT Function
The fourth and final solution we want to consider is how to use the SUBTOTAL Function to calculate the Total in column I. The SUMPRODUCT function returns a subtotal in a list or database.
- In cell H2, execute the formula: =SUBTOTAL(6,fswitch[@[Quantity]:[Price]])
- Click Enter
The formula automatically fill down the result. See the result below:
Conclusion
There are myriads of ways to person different operations in Excel and achieve the same result. We’ve learnt four ways of performing Total in Excel. See you in the next tutorial.