You need to follow the steps below to apply this method. For this, you can write the numbers directly in the formula with mathematical operators. The first method is based on two numbers. We will explain each of these methods one by one below. Lastly, you can create a list of the range from where you want to get the output and use that in the formula. You can simply write the numbers with operators. We can perform this with 3 different methods. There can be two numbers based on which you might want to sum from a range. SUMIFS Between Two Values: Alternative to Excel SUMIF More Methods with Excel SUMIFS Function instead of SUMIF for Two Values 1. Or, you can generate the subtraction by entering ( =) equal sign in Cell F6, drift and click the mouse on Cell F5, type ( –) minus sign, then drift and click on Cell G5.įinally, we have generated the results perfectly. Use the following SUMIF formula and get the total of the cells which satisfy the defined condition.=SUMIF(D5:D10,C5:C10)Īs the above formula works as an array formula, there are two outputs in two Cells, F5 & G5. Remember that the table data is not filtered. We will calculate the sum of all products whose price is greater than 550. We have a dataset of products and their price. Let's understand with an example - Sum a column values based on criteria using SUMIF Now, let's see how the SUMIF function is used to calculate the sum of column data with some conditions on it. In simple terms, you can say that the SUMIF function executes on a normal Excel spreadsheet while the Subtotal function only applies to filtered table data to total a column data. The Subtotal method is used after applying the filter to the column, whereas SUMIF can be used directly on a table with the condition.

This function helps to total the column data only, which satisfies the defined criteria.Īlthough, SUMIF is a bit similar to the Subtotal method because both SUMIF and Subtotal method does not total the entire column data. SUMIF function is a function used in an Excel spreadsheet to total a column value with some condition. The SUMIF function returns the sum of the cells of a column that meets or satisfies the specified condition. If we omit this part of the function the SUMIF function treats 'range' as 'sum_range' thus adds the cells of the range argument. Sum _range: It provides the actual cells that are to be added.

Range: It refers to the range of cells that you want to evaluate to shortlist the cells that meet the given criteria.Ĭriteria: It refers to conditions that tell which cells are to be added. The SUMIF function has three main parameters, which are permanent:

Excel sumif