What is SUMIFS function in Excel? SUMIFS function is one of the Math and Trig functions in Microsoft Excel that adds all of its arguments that meet multiple criteria. For example, you would use SUMIFS to sum the number of retailers in the country who (1) reside in a single zip code and (2) whose profits exceed a specific dollar…
Tag: SUMIFS function
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 feed table ranges into the…
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 works This formula demonstrates a…
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 uses the SUMPRODUCT function to…
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 IF formula with a single IF…
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 criteria. In the example shown,…
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 works The SUMIFS function can…
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” (B4:B9). How this formula works…
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” is the named range E5:E16.…
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 works The SUMIFS function is…
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 or SUMIFS function. The formula…
Sum if between in Excel
This tutorial shows how to Sum if between in Excel using the example below; Formula =SUMIFS(sum_range,criteria_range,”>500″,criteria_range,”<1000″) Explanation To sum if between, you can use the SUMIFS with two criteria. In the example shown, cell G5 contains this formula: =SUMIFS(amount,amount,”>500″,amount,”<1000″) Where “amount” is a named range for cells D5:D11. This formula sums the amounts in column D when they are greater than 500…
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 value in column C begins…
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 a value in column C…
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 items in column C that contain…
Sum if by year in Excel
This tutorial shows how to Sum if by year in Excel using the example below; Formula =SUMIFS(sum_range,date,”>=”&DATE(year),date,”<=”&DATE(year,12,31)) Explanation If you need to sum if by year, you can use the SUMIFS function with two criteria. In the example shown, the formula in H6 is: =SUMIFS(amount,date,”>=”&DATE(G6,1,1),date,”<=”&DATE(G6,12,31)) The result is a total of amounts for 2011. When copied down, the formula also…
Sum if cells are not equal to in Excel
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 in column E only when…
Sum if date is between in Excel
This tutorial shows how to Sum if date is between in Excel using the example below; Formula =SUMIFS(amount,start_date,”>”&A1,end_date,”<“&B1) Explanation To sum values that are between two dates, you can use the SUMIFS function. In the example shown, cell H5 contains this formula: =SUMIFS(amount,date,”>”&H5,date,”<“&H6) This formula sums the amounts in column D when a date in column C is between a…
Sum if date is greater than in Excel
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 when a date in column…
Sum if ends with in Excel
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 named range item (C5:C11) end with “hat”.…
Sum if greater than in Excel
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 D when they are greater…
Sum if less than in Excel
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 they are less than 1000.…
Sum if not blank in Excel
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 in column C is not…
SUMIFS with multiple criteria and OR logic in Excel
This tutorial shows how to SUMIFS with multiple criteria and OR logic in Excel using the example below: Formula =SUM(SUMIFS(sum_range,criteria_range,{“red”,”blue”})) Explanation To sum based on multiple criteria using OR logic, you can use the SUMIFS function with an array constant. In the example shown, the formula in H6 is: =SUM(SUMIFS(E4:E11,D4:D11,{“complete”,”pending”})) How this formula works By default, the SUMIFS function only allows…
Sum sales in last 30 days by ID in Excel
This tutorial shows how to Sum sales in last 30 days by ID in Excel using the example below: Formula =SUMIFS(amounts,dates,”>=”&TODAY()-30,ids,id) Explanation To sum sales in the last 30 days by an id (like name, initials, region, etc.) you can use the SUMIFS function together with the TODAY function. In the example shown, the formula in G5, copied down, is: =SUMIFS($D$5:$D$104,$C$5:$C$104,”>=”&TODAY()-30,$B$5:$B$104,F5)…
Count and Sum Functions in Excel
The most used functions in Excel are the functions that count and sum. COUNT function is one of the STATISTICAL functions while SUM function is one of the Math and Trig functions. You can count and sum based on one criteria or multiple criteria. Count To count the number of cells that contain numbers, use the COUNT function. Countif To count cells based on one criteria (for…