How to Count Visible and Hidden Rows in Excel
This Excel video teaches you how to use advanced functions like SUMPRODUCT, SUBTOTAL, and OFFSET to count visible and hidden rows that meet certain criteria.
The main focus is on counting the number of rows where a specific condition is met, like when the "Plant" is labeled as "C10".
Traditional COUNTIFS function is limited when dealing with hidden rows, so this complex formula combines different functions to achieve the desired result.
The formula involves creating lists of TRUE and FALSE values based on the criteria, using SUBTOTAL to handle visibility, and OFFSET to feed individual cells for counting. Ultimately, SUMPRODUCT is used to multiply these lists and sum up the results, providing an accurate count of visible rows that match the given criteria.
The formula's complexity arises from the need to work with multiple cells and ensure accurate visibility calculations.
EXCEL PRIMED RECOMMENDED COURSES
**Excel Essentials - The A-Z Guide for Advanced Users:
https://excelprimed.com/courses/excel...
**Excel Essentials - The A-Z Guide for Beginners Level I:
https://excelprimed.com/courses/excel...
**Excel Essentials - The A-Z Guide for Beginners Level II:
https://excelprimed.com/courses/excel...
**Excel Essentials - The A-Z Guide for Intermediate Users:
https://excelprimed.com/courses/excel...
**Excel Formulas - Advanced Level I:
https://excelprimed.com/courses/excel...
**Excel Formulas - Advanced Level II:
https://excelprimed.com/courses/excel...
**Excel Formulas - Beginners Level I:
https://excelprimed.com/courses/excel...
**Excel Formulas - Beginners Level II:
https://excelprimed.com/courses/excel...
**Excel Formulas - Intermediate Level I:
https://excelprimed.com/courses/excel...
**Excel Formulas - Intermediate Level II:
https://excelprimed.com/courses/excel...
**Excel Data & Text Manipulation - Advanced Users:
https://excelprimed.com/courses/excel...
**Excel Data & Text Manipulation - Beginners:
https://excelprimed.com/courses/excel...
**Excel Data & Text Manipulation - Intermediate Users:
https://excelprimed.com/courses/excel...