SUMIFS 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 conditionally sum numeric data in an Excel table using SUMIFS

To conditional sum numeric data in an Excel table, you can use SUMIFS with structured references to for both sum and criteria ranges. Formula =SUMIFS(Table[sum_col],Table[crit_col],criteria) Explanation In the example shown, the formula in I5 is: =SUMIFS(Table1[Total],Table1[Item],H5) Where Table1 is an Excel Table with the data range B105:F89. How this formula works This formula uses structured references to… read more »

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 »

Two ways to sum time over 30 minutes in Excel

To sum the total amount of time over 30 minutes, given a set of times that represent duration, you can use the SUMPRODUCT and TIME functions. Alternatively, use SUMIFS and COUNTIFS functions. Formula =SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0))) Explanation  In the example shown, the formula in G5 is: =SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0))) where “times” is the named range C5:C14. How this formula works This formula… read more »

IF with boolean logic in Excel

This tutorial shows how to calculate IF with boolean logic in Excel using the example below; Formula = IF(criteria1*criteria2*criteria3,result) Explanation In the example shown, the formula in F8 is: {=SUM(IF((color=”red”)*(region=”East”)*(quantity>7),quantity))} Note: this is an array formula, and must be entered with control + shift + enter. How this formula works Note: This example demonstrates how to replace a nested… read more »

SUMIFS multiple criteria lookup in table in Excel

This tutorial shows how to calculate SUMIFS multiple criteria lookup in table in Excel using the example below; Formula =SUMIFS(table[values],table[col1],c1,table[col2],c2,table[col3],c3) Explanation In some situations, you can use the SUMIFS function to perform multiple-criteria lookups on numeric data. To use SUMIFS like this, the lookup values must be numeric and unique to each set of possible… read more »

Sum by week in Excel

This tutorial shows how to Sum by week in Excel using the example below; Formula =SUMIFS(value,date,”>=”&A1,date,”<“&A1+7) Explanation To sum by week, you can use a formula based on the SUMIFS function. In the example shown, the formula in F4 is: =SUMIFS(amount,date,”>=”&E4,date,”<“&E4+7) This formula uses the named ranges “amount” (C4:C21) and “date” (B4:B21). How this formula… read more »

Sum by month in Excel

This tutorial shows how to Sum by month in Excel using the example below; Formula =SUMIFS(values,date_range,”>=”&A1,date_range,”<=”&EOMONTH(A1,0)) Explanation To sum by month, you can use a formula based on the SUMIFS function, with help from the EOMONTH function. In the example shown, the formula in F4 is: =SUMIFS(amount,date,”>=”&E4,date,”<=”&EOMONTH(E4,0)) This formula uses the named ranges “amount” (C4:C9) and “date”… read more »

Sum by week number in Excel

This tutorial shows how to Sum by week number in Excel using the example below; Formula =SUMIFS(sumrange,weekrange,week) Explanation To sum by week, you can use a formula based on the SUMIFS function. In the example shown, the formula in H5 is: =SUMIFS(total,color,$G5,week,H$4) where “total” is the named range D5:D16, “color” is the named range B5:B16, and “week”… read more »

Sum if cells contain both x and y in Excel

This tutorial shows how to Sum if cells contain both x and y in Excel using the example below; Formula =SUMIFS(range1,range2,”*cat*”,range2,”*rat*”) Explanation To sum if cells contain both x and y (i.e. contain “cat” and “rat”, in the same cell) you can use the SUMIFS function. In the example shown, the formula in F5 is: =SUMIFS(C4:C8,B4:B8,”*cat*”,B4:B8,”*rat*”) How this formula… 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 »

Sidebar