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

Data Analysis

  • How to sum a total in multiple Excel tables
  • Subtotal function in Excel
  • How to create running total in an Excel Table
  • Everything about Charts in Excel
  • Chart Axes in Excel

References

  • How to get address of first cell in range in Excel
  • How to use Excel OFFSET function
  • How to use Excel LOOKUP Function
  • How to get first column number in range in Excel
  • How to use Excel COLUMN Function

Data Validations

  • Excel Data validation require unique number
  • Excel Data validation don’t exceed total
  • Prevent invalid data entering in specific cells
  • Excel Data validation must contain specific text
  • Data validation must not exist in list

Tag: SUBTOTAL function

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

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

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

  • Excel If, Nested If, And/Or Criteria Examples
  • Invoice status with nested if in Excel
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • Not Equal To ‘<>‘ operator in Excel
  • How to use Excel NOT Function

Date Time

  • How to calculate months between dates in Excel
  • EDATE function: Description, Usage, Syntax, Examples and Explanation
  • Two ways to sum time over 30 minutes in Excel
  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation
  • Get last weekday in month in Excel

Grouping

  • How to randomly assign people to groups in Excel
  • Calculate conditional mode with criteria in Excel
  • Group times into unequal buckets in Excel
  • Group arbitrary text values in Excel
  • Map text to numbers in Excel

General

  • Automatically fill series of cells in Excel using AutoFill
  • Basic numeric sort formula in Excel
  • Convert column number to letter in Excel
  • How to choose page/paper size in Excel before Printing
  • Check if multiple cells have same value with case sensitive in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning