If we want to add one more criteria, we can add it in the same formula. To get the sum of both, we have used another SUM function which will add the sum of two sales. When we gave SUMIFS function with two criteria as in the form of an array, it will calculate the sum of sales through directly and online separately. I will explain why we used another SUM function and how it works. Observe the formula in the above screenshot one more SUM added to the SUMIF, and the result is 2274.
Hence, if we add one more SUM formula to SUMIFS, it will perform both criteria. The formula did not pick the sales through online because we gave the formula in a different format that is like an array. Observe the above screenshot that 1438 is the total sales for sales through direct. Then how this 1438 comes and what sum is this. So, the total should be 2274, but we got the result of 1438. Observe the below screenshot I have highlighted the count and sum of the values. Apply a filter and filter only sales through direct and sales through online and select the entire quantity and observe the total at the bottom of the screen. See, we got the result as 1438 let’s check whether it picked the total correctly or not. We will give criteria1 and criteria2 in a curly bracket like an array. Later we need to give the Criteria1 and then criteria _range2, criteria 2, but here we will do a small change.
Here Criteria is “Sales through direct” and “Sales through online” hence we need to select the column B data from B2 to B10. The next step is the selection of criteria_range1. Observe the above screenshot the quantity is the column we need to add hence select the cells from C2 to C10 as sum_range. Here sum range means the column where the values are available to perform addition or sum. It is a bit different from the SUMIF as in this first we will select the sum range.
Now we will apply the SUMIFS formula to find the total sales. Our task is calculating the total value of sales, whether it is through direct or online. We will now consider a small table with data of sales and revenue through online and direct as below. If we are using OR logic, then we can perform SUM calculation for dual criteria.įor using OR logic, we should use SUMIFS instead of SUMIF because SUMIF can perform with single criteria, but SUMIFS can perform on multiple criteria as per our requirement. Normally SUMIF will work on the logic, AND hence that is the reason where ever the criteria match, it will perform the addition and return the results.īy using normal SUMIF, we will be able to perform SUM operation for only single criteria. Make sure you are using the commas and a double column for criteria otherwise, the formula will throw an error. Similarly, we can find the sales of Y and Z also. The sum of sales of X across all the three companies is 11166.