SUMIF function in Excel

Excel Data Analysis is a powerful tool that is used to visualize and gain insights of records in a spreadsheet.

How to calculate current stock or inventory in Excel

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… read more »

Sum lookup values using SUMIF in Excel

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… read more »

Subtotal by color in Excel

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,… read more »

Subtotal by invoice number in Excel

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… read more »

Sum by group in Excel

This tutorial shows how to Sum by group in Excel using the example below; Formula =IF(A2=A1,””,SUMIF(A:A,A2,B:B)) 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… read more »

3D SUMIF for multiple worksheets in Excel

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: =SUMPRODUCT(SUMIF(INDIRECT(“‘”&sheets&”‘!”&”D4:D5”),B9,INDIRECT(“‘”&sheets&”‘!”&”E4:E5”)))… read more »

Sum if cells are equal to in Excel

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… read more »

Sum if begins with in Excel

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… read more »

Sum if cells contain specific text in Excel

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… read more »

Sum if cell contains text in another cell in Excel

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… read more »

Sidebar