What is SUMIF function in Excel? SUMIF function is one of the Math and Trig functions in Microsoft Excel that is used to sum the values in a range that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger …
This tutorial covers basic inventory formula. To calculate current stock, or inventory, you can use Excel Tables with a formula based on the SUMIF function. Formula =SUMIFS(In[Qty],In[Color],A1)-SUMIFS(Out[Qty],Out[Color],A1) Explanation In the example shown, the formula in K7 is: =SUMIFS(In[Qty],In[Color],J7)-SUMIFS(Out[Qty],Out[Color],J7) Where “In” is the Excel Table on the left, “Out” is the table in the middle. How this formula …
This tutorial shows how to Sum lookup values using SUMIF in Excel using the example below; Formula =SUMPRODUCT(SUMIF(codes,lookups,values)) Explanation To sum values retrieved by a lookup operation, you can use SUMPRODUCT with the SUMIF function. In the example shown, the formula in H5 is: =SUMPRODUCT(SUMIF(codes,C5:G5,values)) Where codes is the named range J4:J5 and values is …
This tutorial shows how to Subtotal by color in Excel using the example below; If you need to subtotal numbers by color, you can easily do so with the SUMIF function. Formula =SUMIF(color_range,criteria,number_range) Explanation In the example shown, the formula in G5 is: =SUMIF($B$4:$B$11,F5,$D$4:$D$11) How this formula works The SUMIF function takes three arguments: range, …
This tutorial shows how to Subtotal by invoice number in Excel using the example below; Formula =IF(COUNTIF(range,criteria)=1,SUMIF(range,criteria,sumrange,””) Explanation To subtotal values by invoice number, you can use a formula based on COUNTIF and SUMIF. In the example shown, the formula in E5 is: =IF(COUNTIF($B$5:B5,B5)=1,SUMIF($B:$B,B5,$D:$D),””) How this formula works This formula uses COUNTIF with an expanding range to …
This tutorial shows how to Sum by group in Excel using the example below; Formula =IF(B5=B4,””,SUMIF(B:B,B5,C:C)) Explanation To subtotal data by group or label, directly in a table, you can use a formula based on the SUMIF function. In the example shown, the formula in D5 is: =IF(B5=B4,””,SUMIF(B:B,B5,C:C)) Note: data must be sorted by the …
This tutorial shows how to 3D SUMIF for multiple worksheets in Excel using the example below; Formula =SUMPRODUCT(SUMIF(INDIRECT (“‘”&sheets&”‘!”&”range”),criteria, INDIRECT(“‘”&sheets&”‘!”&”sumrange”))) Explanation To conditionally sum identical ranges that exist in separate worksheets, all in one formula, you can do so with the SUMIF function + INDIRECT, wrapped in SUMPRODUCT. In the example, the formula looks like …
This tutorial shows how to Sum if cells are equal to in Excel using the example below; In the example shown, we are summing all sales in the West region. Explanation If you need to sum numbers based on other cells being equal to a certain value, you can easily do with either the SUMIF …
This tutorial shows how to Sum if begins with in Excel using the example below; Formula =SUMIF(range,”text*”,sum_range) Explanation To sum cells if other cells begin with a specific value, you can use the SUMIF function. In the example shown, cell G6 contains this formula: =SUMIF(C5:C11,”t-shirt*”,D5:D11) This formula sums the amounts in column D when a …
This tutorial shows how to Sum if cells contain specific text in Excel using the example below; Formula =SUMIF(range,”*text*”,sum_range) Explanation To sum if cells contain specific text, you can use the SUMIF function with a wildcard. In the example shown, cell G6 contains this formula: =SUMIF(C5:C11,”*t-shirt*”,D5:D11) This formula sums the amounts in column D when …
This tutorial shows how to Sum if cell contains text in another cell in Excel using the example below; Formula =SUMIF(range,”*”&A1&”*”,sum_range) Explanation To sum if cells contain specific text in another cell, you can use the SUMIF function with a wildcard and concatenation. In the example shown, cell G6 contains this formula: =SUMIF(C5:C11,”*”&F6&”*”,D5:D11) This formula sums amounts for …
This tutorial shows how to Sum if cells are not equal to in Excel using the example below; Formula =SUMIF(range,”<>value”,sum_range) Explanation To sum cells when other cells are not equal to a specific value, you can use the SUMIF function. In the example shown, cell H7 contains this formula: =SUMIF(region,”<>West”,amount) This formula sums the amounts …
This tutorial shows how to Sum if date is greater than in Excel using the example below; Formula =SUMIF(range,”>”&DATE(year,month,day),sum_range) Explanation To sum if based on dates greater than a certain date, you can use the SUMIF function. In the example shown, cell H5 contains this formula: =SUMIF(date,”>”&DATE(2015,10,1),amount) This formula sums the amounts in column D …
This tutorial shows how to Sum if equal to one of many things in Excel using the example below; Formula =SUMPRODUCT(SUMIF(range,things,values)) Explanation If you need to sum values when cells are equal to one of many things, you can use a formula based on the SUMIF and SUMPRODUCT functions. In the example shown, the formula in …
This tutorial shows how to Sum if ends with in Excel using the example below; Formula =SUMIF(range,”*text”,sum_range) Explanation To sum if cells end with specific text, you can use the SUMIF function. In the example shown, cell G6 contains this formula: =SUMIF(item,”*hat”,amount) This formula sums cells in the named range amount (D5:D11) only if cells in the …
This tutorial shows how to Sum if equal to either x or y in Excel using the example below; In the example, we are summing all sales from either the West OR North region. Explanation If you need to sum numbers based on other cells being equal to either one value or another (either x …
This tutorial shows how to Sum if greater than in Excel using the example below; Formula =SUMIF(range,”>1000″) Explanation To sum if greater than, you can use the SUMIF function. In the example shown, cell H6 contains this formula: =SUMIF(amount,”>1000″) Where “amount” is a named range for cells D5:D11. This formula sums the amounts in column …
This tutorial shows how to Sum if less than in Excel using the example below; Formula =SUMIF(range,”<1000″) Explanation To sum if less than, you can use the SUMIF function. In the example shown, cell H5 contains this formula: =SUMIF(amount,”<1000″) Where “amount” is a named range for cells D5:D11. This formula sums the amounts in column D when …
This tutorial shows how to Sum if not blank in Excel using the example below; Formula =SUMIF(range,”<>”,sum_range) Explanation To sum cells when certain values are not blank, you can use the SUMIF function. In the example shown, cell G6 contains this formula: =SUMIF(C5:C11,”<>”,D5:D11) This formula sums the amounts in column D only when the value …
The powerful SUMIF function in Excel sums cells based on one criteria. This page contains many easy to follow SUMIF examples. Numbers 1. The SUMIF function below (two arguments) sums values in the range A1:A5 that are less than or equal to 10. 2. The following SUMIF function gives the exact same result. It uses the & operator to …
In addition to formulas, another way to conduct mathematical computations in Excel is through functions. Statistical functions apply a mathematical process to a group of cells in a worksheet. This chapter gives an overview of some very useful statistical functions like: AVERAGE function, AVERAGEIF function, MEDIAN function, SUMIF function, MIN function, MODE function, STEDV function, LARGE function, SMALL function. Average To calculate the average of a …