The formula shown here uses Excel’s COUNTA function together with the INDEX function to create a dynamic range to count.
The INDEX function is used to provide a cell reference to the COUNTA function for the position of the top range as well as the position of the bottom range for each subset.
Excel’s MATCH function is used to look for or match for the first non empty cell (non blank cell) to provide the location of the top cell address and then the MATCH function is used again to match for the first empty cell to provide that address for the bottom of the range to the COUNTA function.
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
