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

Data Analysis

  • How To Create Frequency Distribution in Excel
  • How to Use Solver Tool in Excel
  • Excel Frequency Function Example
  • Conditional Formatting Color Scales Examples in Excel
  • Remove Duplicates Example in Excel

References

  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to get first row number in range in Excel
  • Two-way lookup with VLOOKUP in Excel
  • Get nth match with INDEX / MATCH in Excel
  • How to get relative row numbers in a range in Excel

Data Validations

  • Excel Data validation with conditional list
  • Excel Data validation only dates between
  • Prevent invalid data entering in specific cells
  • Excel Data validation number multiple 100
  • Excel Data validation must begin with

Tag: SUMIF function

SUMIF function in Excel

by

The powerful SUMIF function in Excel sums cells based on one criteria. This page contains many easy to follow SUMIF examples. Numbers 1. The SUMIF function below (two arguments) sums values in the range A1:A5 that are less than or equal to 10. 2. The following SUMIF function gives the exact same result. It uses the & operator to …

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 lookup values using SUMIF in Excel

by

This tutorial shows how to Sum lookup values using SUMIF in Excel using the example below; Formula =SUMPRODUCT(SUMIF(codes,lookups,values)) Explanation To sum values retrieved by a lookup operation, you can use SUMPRODUCT with the SUMIF function. In the example shown, the formula in H5 is: =SUMPRODUCT(SUMIF(codes,C5:G5,values)) Where codes is the named range J4:J5 and values is …

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 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 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 equal to either x or y in Excel

by

This tutorial shows how to Sum if equal to either x or y in Excel using the example below; In the example, we are summing all sales from either the West OR North region. Explanation If you need to sum numbers based on other cells being equal to either one value or another (either x …

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 equal to one of many things in Excel

by

This tutorial shows how to Sum if equal to one of many things in Excel using the example below; Formula =SUMPRODUCT(SUMIF(range,things,values)) Explanation If you need to sum values when cells are equal to one of many things, you can use a formula based on the SUMIF and SUMPRODUCT functions. In the example shown, the formula in …

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

3D SUMIF for multiple worksheets in Excel

by

This tutorial shows how to 3D SUMIF for multiple worksheets in Excel using the example below; Formula =SUMPRODUCT(SUMIF(INDIRECT (“‘”&sheets&”‘!”&”range”),criteria, INDIRECT(“‘”&sheets&”‘!”&”sumrange”))) Explanation To conditionally sum identical ranges that exist in separate worksheets, all in one formula, you can do so with the SUMIF function + INDIRECT, wrapped in SUMPRODUCT. In the example, the formula looks like …

Continue Reading

Sum by group in Excel

by

This tutorial shows how to Sum by group in Excel using the example below; Formula =IF(B5=B4,””,SUMIF(B:B,B5,C:C)) Explanation To subtotal data by group or label, directly in a table, you can use a formula based on the SUMIF function. In the example shown, the formula in D5 is: =IF(B5=B4,””,SUMIF(B:B,B5,C:C)) Note: data must be sorted by the …

Continue Reading

Subtotal by invoice number in Excel

by

This tutorial shows how to Subtotal by invoice number in Excel using the example below; Formula =IF(COUNTIF(range,criteria)=1,SUMIF(range,criteria,sumrange,””) Explanation To subtotal values by invoice number, you can use a formula based on COUNTIF and SUMIF. In the example shown, the formula in E5 is: =IF(COUNTIF($B$5:B5,B5)=1,SUMIF($B:$B,B5,$D:$D),””) How this formula works This formula uses COUNTIF with an expanding range to …

Continue Reading

Subtotal by color in Excel

by

This tutorial shows how to Subtotal by color in Excel using the example below; If you need to subtotal numbers by color, you can easily do so with the SUMIF function. Formula =SUMIF(color_range,criteria,number_range) Explanation In the example shown, the formula in G5 is: =SUMIF($B$4:$B$11,F5,$D$4:$D$11) How this formula works The SUMIF function takes three arguments: range, …

Continue Reading

9 Mathematical Computations Example using Excel Statistical Function

by

In addition to formulas, another way to conduct mathematical computations in Excel is through functions. Statistical functions apply a mathematical process to a group of cells in a worksheet. This chapter gives an overview of some very useful statistical functions like: AVERAGE function, AVERAGEIF function, MEDIAN function, SUMIF function, MIN function, MODE function, STEDV function, LARGE function, SMALL function. Average To calculate the average of a …

Continue Reading

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

by

What is SUMIF function in Excel? SUMIF function is one of the Math and Trig functions in Microsoft Excel that is used to sum the values in a range that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger …

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

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

  • How to use IFS function in Excel
  • How to use Excel AND Function
  • How to use Excel TRUE Function
  • How to use Excel FALSE Function
  • Extract multiple matches into separate rows in Excel

Date Time

  • Display the current date in Excel
  • Display Date is same month in Excel
  • Pad week numbers with zeros in Excel
  • Convert Excel time to Unix time in Excel
  • WEEKNUM function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group times into 3 hour buckets in Excel
  • Group numbers with VLOOKUP in Excel
  • How to randomly assign people to groups in Excel
  • How to randomly assign data to groups in Excel
  • Categorize text with keywords in Excel

General

  • Advanced Number Formats in Excel
  • Delete Blank Rows at Once in Excel
  • Check if multiple cells have same value in Excel
  • Basic text sort formula in Excel
  • Convert column number to letter in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning