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

Data Analysis

  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • Subtotal function in Excel
  • Example of COUNTIFS with variable table column in Excel
  • Number and Text Filters Examples in Excel
  • How to combine 2 or more chart types in a single chart in Excel

References

  • How to get last row in numeric data in Excel
  • Approximate match with multiple criteria in Excel
  • Lookup entire row in Excel
  • Excel Advanced Lookup using Index and Match Functions
  • How to get last column number in range in Excel

Data Validations

  • Excel Data validation with conditional list
  • Prevent invalid data entering in specific cells
  • Excel Data validation must not contain
  • Excel Data validation must begin with
  • Excel Data validation unique values only

Tag: AGGREGATE function

How to Sum Range with Errors in Excel

by

This example shows you how to create an array formula that sums a range with errors. You can also use the AGGREGATE function to sum a range with errors. 1. We use the IFERROR function to check for an error. Explanation: the IFERROR function returns 0, if an error is found. If not, it returns the value of the cell. …

Continue Reading

Extract all partial matches in Excel

by

This tutorial shows how to Extract all partial matches in Excel using the example below; Formula =IF(F5>ct,””,INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5))) Explanation To extract all matches based on a partial match, you can use use an array formula based on the INDEX and AGGREGATE functions, with support from ISNUMBER and SEARCH. In the example shown, the formula in G5 …

Continue Reading

How To Use AGGREGATE function to sum a range with errors in Excel

by

Excel functions such as SUM, COUNT, LARGE and MAX don’t work if a range includes errors. However, you can easily use the AGGREGATE function to fix this. 1. For example, Excel returns an error if you use the SUM function to sum a range with errors. 2. Use the AGGREGATE function to sum a range with errors. Explanation: …

Continue Reading

Average and ignore errors in Excel

by

This tutorial shows how to work Average and ignore errors in Excel using the example below; Formula =AVERAGEIF(values,”>=0″) Explanation To average a list of values, ignoring any errors that might exist in the range, you can use the AVERAGEIF or AGGREGATE function, as described below. In the example shown, the formula in E5 is: =AVERAGEIF(values,”>=0″) …

Continue Reading

Max value ignore all errors in Excel

by

This tutorial shows how to calculate Max value ignore all errors in Excel using the example below; Formula =AGGREGATE(4,6,values) Explanation To get the maximum value in numeric data while ignoring all errors, you can use the AGGREGATE function, as explained below. In the example shown, the formula in E5 is: =AGGREGATE(4,6,values) where “values” is the named …

Continue Reading

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

by

What is AGGREGATE function in Excel? AGGREGATE function is one of the Math and Trig functions in Microsoft Excel that returns an aggregate in a list or database. The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values. Syntax of AGGREGATE function Reference …

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 Excel XOR Function
  • SWITCH function example in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • Not Equal To ‘<>‘ operator in Excel
  • How to use Excel TRUE Function

Date Time

  • Pad week numbers with zeros in Excel
  • Display Days in month in Excel
  • Get days between dates in Excel
  • Add years to date in Excel
  • Count day of week between dates in Excel

Grouping

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

General

  • Subtotal by invoice number in Excel
  • Create dynamic workbook reference to another workbook in Excel
  • How to get original number from percent change in Excel
  • Common Errors in Excel
  • Freeze and Unfreeze Panes in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning