How to use summation in Excel

In this case, "B2:B13" refers to the range of cells containing monthly sales, while ">1000" indicates that we want to sum only sales over 1000 euros.

Using the SUMIF function to sum only certain cells

The SUMIF function is another powerful Excel function that allows you to sum only cells that meet certain criteria. This function can be very useful when working with large amounts of data and you want to quickly sum only some of the cells.

Syntax of the SUMIF function

The syntax of the SUMIF function is as follows:

• range: the range of cells you want to sum.
• criteria: the criteria that must be met by the cells to be summed. This parameter can be expressed as a range of cells or as a single value.
• sum_range (optional): the range of cells to sum if the criteria are met. If this parameter is omitted, the function will sum the cells in the range specified in the first parameter (range).

Practical example

Suppose we have a table containing data on monthly sales for a company. We want to calculate the sum of sales made only in summer months (June, July, and August).

In this case, we can use the SUMIF function like this:

In this example, we specified the range of cells containing sales data (B2:B13) as the first parameter of the SUMIF function. As the second parameter, we specified the criteria that must be met by the cells to be summed (in this case, "June", "July", and "August"). Finally, we added the results of three different SUMIF functions to get the total sum of summer sales.

Using the SUMIF function can save a lot of time when working with large amounts of data. Additionally, this function allows for more precise and accurate calculations by limiting the number of cells involved in the sum.

Summing values based on multiple criteria with SUMIFS function

The SUMIFS function is one of the most useful functions in Excel when it comes to summing values based on multiple criteria. This function allows you to specify multiple conditions for the sum, which means that you can filter data and find only those that meet certain requirements.

To use the SUMIFS function, you need to specify three arguments:

• Range: The range of cells from which you want to perform your search.
• Criteria_range1: The range of cells containing the criteria for the first column.
• Somme_riga1: the range of cells containing the values to be summed in the first column.

Here's an example: let's say we have a list of sales for different products and regions. We want to sum up the total sales for all products sold in the "North" region in "January". To do this, we can use the SUMIFS function as follows:

In this case, the Lookup_array is the range B2:E11, which contains all the sales data. The Criteria_range1 are the cells A2:A11, which contain the names of the regions. The string "North" specifies the criteria for summing in the first column. The Sum_range1 are the cells C1:E1, which contain the names of the months. The string "January" specifies the criteria for summing in the first row.

You can use the SUMIFS function with any number of criteria and sum ranges. This means you can filter data based on multiple conditions and find only those that meet certain requirements.

Using the PRODUCT.SUM function to multiply and sum values simultaneously

The PRODUCT.SUM function in Excel is an advanced formula that allows you to multiply two or more ranges of cells and then sum their result. This function can be very useful when working with large amounts of data and you want to quickly get a total value.

To use the PRODUCT.SUM function, you need to follow these steps:

1. Select the cell where you want to display the result;
2. Enter the PRODUCT.SUM formula in the selected cell;
3. Specify the ranges of cells to multiply as arguments of the function.

Here's an example to better understand how it works:

Let's say we have a table containing monthly sales of three products (A, B and C) in three different regions (North, Central and South). We want to calculate the total revenue generated by each product in each region. To do this, we can use the PRODUCT.SUM function.

In this case, we will select the cell where we want to display the total revenue result for product A in the North region. We will then enter the following formula:

Where:

• B2:D2 represents the range of cells containing monthly sales of product A in the North region;
• B6:D6 represents the range of cells containing unit prices for product A.

The formula multiplies the values ​​of the two matrices and then sums up the result, returning the total revenue for product A in the North region.

You can repeat the same process to calculate total revenue for each product in each region. This way, you can get a complete view of your sales and total earnings.

Conclusion

The SUMPRODUCT function is a powerful tool that can help you save time and effort when calculating large amounts of data in Excel. Knowing how to use it will allow you to analyze your data more efficiently and accurately, providing valuable information about your business activities. We hope this guide has been helpful to you and we encourage you to experiment with the SUMPRODUCT function to discover all its potential.