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 …
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 …
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 …
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 …
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 …
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 …
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 …
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” …
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, …
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 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 …
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 …
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 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 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 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 …
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 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 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 …
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 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 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” …
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 …
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 …
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 …