Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conditional Formatting New Rule with Formulas in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • Get column name from index in Excel Table
  • How to create Checklist in Excel
  • Conditional Formatting Data bars Examples in Excel

References

  • Basic INDEX MATCH approximate in Excel
  • Get nth match with INDEX / MATCH in Excel
  • Vlookup Examples in Excel
  • Extract data with helper column in Excel
  • Convert text string to valid reference in Excel using Indirect function

Data Validations

  • Data validation must not exist in list
  • Excel Data validation allow weekday only
  • Excel Data validation date in next 30 days
  • Excel Data validation must not contain
  • Excel Data validation date in specific year

Tag: SUMIFS function

Count and Sum Functions in Excel

by

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 …

Continue Reading

SUMIFS function: Description, Usage, Syntax, Examples and Explanation

by

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 …

Continue Reading

How to calculate current stock or inventory in Excel

by

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 …

Continue Reading

How to conditionally sum numeric data in an Excel table using SUMIFS

by

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 …

Continue Reading

Two ways to sum time over 30 minutes in Excel

by

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 …

Continue Reading

IF with boolean logic in Excel

by

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 …

Continue Reading

SUMIFS multiple criteria lookup in table in Excel

by

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 …

Continue Reading

Sum by week number in Excel

by

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

Continue Reading

SUMIFS with multiple criteria and OR logic in Excel

by

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

Continue Reading

Sum if begins with in Excel

by

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 …

Continue Reading

Sum if by year in Excel

by

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 …

Continue Reading

Sum if between in Excel

by

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 …

Continue Reading

Sum if cell contains text in another cell in Excel

by

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 …

Continue Reading

Sum if cells are equal to in Excel

by

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 …

Continue Reading

Sum if cells are not equal to in Excel

by

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 …

Continue Reading

Sum if cells contain both x and y in Excel

by

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 …

Continue Reading

Sum if cells contain specific text in Excel

by

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 …

Continue Reading

Sum if date is greater than in Excel

by

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 …

Continue Reading

Sum if date is between in Excel

by

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 …

Continue Reading

Sum if ends with in Excel

by

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 …

Continue Reading

Sum if greater than in Excel

by

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 …

Continue Reading

Sum if less than in Excel

by

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 …

Continue Reading

Sum by month in Excel

by

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

Continue Reading

Sum if not blank in Excel

by

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 …

Continue Reading

Sum by week in Excel

by

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 …

Continue Reading

Sum sales in last 30 days by ID in Excel

by

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 …

Continue Reading

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • OR function: Description, Usage, Syntax, Examples and Explanation
  • IF with wildcards in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel NOT Function
  • Return blank if in Excel

Date Time

  • Convert decimal minutes to Excel time
  • Count day of week between dates in Excel
  • TIMEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • How to join date and text together in Excel
  • Count dates in current month in Excel

Grouping

  • Group arbitrary text values in Excel
  • Map text to numbers in Excel
  • Running count group by n size in Excel
  • Calculate conditional mode with criteria in Excel
  • Group times into 3 hour buckets in Excel

General

  • How to calculate percent sold in Excel
  • 3D SUMIF for multiple worksheets in Excel
  • How to password protect excel sheet?
  • How to get random value from list or table in Excel
  • Automatically fill series of cells in Excel using AutoFill
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning