Learn how to use SUMIF function in Excel to quickly summarize data based on specific criteria. Step-by-step guide with examples.
Excel's SUMIF function allows you to take the sum of a column or row of data conditional upon a particular criteria row.
=SUMIF(D5:D10,C13,G5:G10) Press Enter. In the SUMIF function, D5:D10 is declared as range, C13 as criteria, and G5:G10 as sum_range. The formula returns the summation of values in the sum_range that match the criteria. You can also solve this problem using the SUMIFS functio...
In Cell F18, insert: =INDEX(D5:I14,MATCH(F17,B5:B14,0),MATCH(F16,D4:I4,0)) Hit Enter. Part 2 – Using SUMIF with INDEX and MATCH Functions in Excel We’ve modified the dataset to allow duplicated values for brand-device combinations. We’ll find the total sales of Lenovo device...
As you see, using the VLOOKUP and SUM functions in Excel is easy. However, this is not the ideal solution, especially if you are working with big tables. The point is that using array formulas may adversely affect the workbook's performance since each value in the array makes a separate ...
Note: remember, when using the SUMIFS function, the first argument is the range to sum, followed by two or more range/criteria pairs. Or Criteria Summing with Or criteria in Excel can be tricky. 1. The formula below sums the cells that meet the following criteria: Google or Facebook (on...
As you see, the syntax of the Excel SUMIF function allows for one condition only. And still, we say that Excel SUMIF can be used to sum values with multiple criteria. How can that be? By adding the results of several SUMIF functions and by using SUMIF formulas with array criteria, ...
=SUMIF(B2:B7,"<10000",C2:C7) Using the following formula, you can add the expenses in cells B2 through B7 only if the revenue in cells C2 through C7 is greater than 25,000. =SUMIF(C2:C7,">25000",B2:B7) You can also replace the actual value in the formula with one contained ...
Sumif in Excel with Multiple Criteria In the SUMIF function, we can add two criteria in a single range, i.e., we can use SUMIF for Multiple criteria. Let us understand this with some examples. UsingSUMwithSUMIFfunction: Example #1 ...
Sum_range: The range to be sum or cells to be added. The Sum_range is optional. The formula is SUMIF (range, criteria, [sum_range]). SUMIFS Syntax Sum_range: the range of cells to add. Criteria_range 1: The range of cells that is analyzed using criteria 1. Criteria 1: Deter...