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

Data Analysis

  • Conditional Formatting Color Scales Examples in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • How to count table columns in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • Excel Line Chart

References

  • How to get first column number in range in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Vlookup Examples in Excel
  • How to use Excel COLUMN Function
  • INDEX function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation no punctuation
  • Excel Data validation must not contain
  • Excel Data validation require unique number
  • Excel Data validation allow weekday only

Tag: SUMIFS function

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

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

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

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

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

  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • How to return blank in place of #DIV/0! error in Excel
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel TRUE Function

Date Time

  • Get fiscal year from date in Excel
  • Create date range from two dates in Excel
  • Steps to create Dynamic calendar grid in Excel
  • Add workdays to date custom weekends in Excel
  • Convert Excel time to decimal hours in Excel

Grouping

  • Group arbitrary text values in Excel
  • Map inputs to arbitrary values in Excel
  • Running count group by n size in Excel
  • How to randomly assign data to groups in Excel
  • Group times into 3 hour buckets in Excel

General

  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • How to generate random date between two dates in Excel
  • How to calculate percent of students absent in Excel
  • How to fill cell ranges with random number from fixed set of options in Excel
  • How to add sequential row numbers to a set of data in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning