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

Data Analysis

  • How to calculate correlation coefficient Correlation in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • How to sum a total in multiple Excel tables
  • Understanding Pivot Tables in Excel
  • How To Perform and Interpret Regression Analysis in Excel

References

  • Excel Advanced Lookup using Index and Match Functions
  • How to get relative column numbers in a range in Excel
  • How to get last column number in range in Excel
  • How to use Excel FORMULATEXT function
  • How to calculate two-way lookup VLOOKUP in Excel Table

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation only dates between
  • Excel Data validation unique values only
  • Excel Data validation allow weekday only
  • Excel Data validation must begin with

Tag: SUBTOTAL function

Subtotal function in Excel

by

The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use. The SUBTOTAL function is designed for columns of data, or vertical ranges. Use the SUBTOTAL function in Excel instead of SUM, COUNT, MAX, etc. to ignore rows hidden by a filter or manually hidden rows. Rows …

Continue Reading

Count visible rows in a filtered list in Excel

by

This tutorial shows how to Count visible rows in a filtered list in Excel using the example below; Formula =SUBTOTAL(3,range) Explanation If you want to count the number of visible items in a filtered list, you can use the SUBTOTAL function, which automatically ignores rows that are hidden by a filter. The SUBTOTAL function can perform calculations …

Continue Reading

Count visible rows only with criteria in Excel

by

This tutorial shows how to Count visible rows only with criteria in Excel using the example below; Formula =SUMPRODUCT((range=criteria)*(SUBTOTAL(3,OFFSET(range,rows,0,1)))) Explanation To count visible rows only with criteria, you can use a rather complex formula based on SUMPRODUCT, SUBTOTAL, and OFFSET. The problem The SUBTOTAL function can easily generate sums and counts for hidden and non-hidden …

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

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 invoices by age in Excel

by

This tutorial shows how to Subtotal invoices by age in Excel using the example below; Formula =SUMIF(age,criteria,amount) Explanation If you need to subtotal invoice amounts by age, you can easily do so with the SUMIF function. In the example shown, the formula in G5 is: =SUMIF(age,G5,amount) Where age is a named range for the range …

Continue Reading

Sum visible rows in a filtered list in Excel

by

This tutorial shows how to Sum visible rows in a filtered list in Excel using the example below; Formula =SUBTOTAL(9,range) Explanation If you want to sum only the visible rows in a filtered list (i.e. only those rows not filtered out), you can use the SUBTOTAL function with function number 9 or 109. What makes SUBTOTAL …

Continue Reading

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

by

What is SUBTOTAL function in Excel? SUBTOTAL function is one of the Math and Trig functions in Microsoft Excel that returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab in the Excel desktop application. Once the subtotal list is …

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

  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel OR Function
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • IF with boolean logic in Excel

Date Time

  • How to calculate quarter from date in Excel
  • Check If Two Dates are same month in Excel
  • How to calculate working days left in month in Excel
  • Count birthdays by month in Excel
  • Calculate expiration date in Excel

Grouping

  • Group numbers with VLOOKUP in Excel
  • Group times into 3 hour buckets in Excel
  • Group numbers at uneven intervals in Excel
  • Map inputs to arbitrary values in Excel
  • How to randomly assign people to groups in Excel

General

  • How to calculate percent change in Excel
  • Split Cell Content Using Text to Columns in Excel
  • Select, Insert, Rename, Move, Delete Worksheets in Excel
  • How to generate random number between two numbers in Excel
  • How to generate random number weighted probability in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning