SUBTOTALES y CONTAR.SI cuando se utiliza Autofiltro en Excel

Introduction

This tutorial will guide you through creating a dynamic formula in Excel that allows you to count filtered data while ignoring hidden cells. We will use a combination of functions: SUBTOTAL, COUNTIF, SUMPRODUCT, OFFSET, ROW, and MIN. This approach is beneficial for users who need to analyze data without being affected by filters, ensuring accurate calculations in reports or data analysis.

Step 1: Understand the Functions

Before diving into the formula, familiarize yourself with the key functions you'll be using:

• SUBTOTAL: This function can perform various operations (like SUM or COUNT) on a filtered range, excluding hidden rows.
• COUNTIF: Counts the number of cells that meet a specified condition.
• SUMPRODUCT: Multiplies corresponding elements in the specified arrays and returns the sum of those products.
• OFFSET: Returns a reference to a range that is offset from a starting cell or range.
• ROW: Returns the row number of a reference.
• MIN: Returns the smallest number in a set of values.

Step 2: Set Up Your Data

1. Open Excel and enter your dataset in a clear format, with headers for each column.
2. Ensure you have a column that you want to filter and count based on specific criteria.

Step 3: Apply Autofilter to Your Data

1. Click on any cell in your dataset.
2. Go to the Data tab in the ribbon.
3. Click on “Filter” to enable the Autofilter feature, which adds dropdown arrows to your header cells.

Step 4: Create the Formula

1. Select the cell where you want the result of the COUNTIF to appear.
2. Enter the following formula, adjusting the ranges as necessary:

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A100, ROW(A2:A100)-MIN(ROW(A2:A100)), 0, 1)), --(B2:B100="YourCriteria"))

• Replace A2:A100 with the range of cells you want to evaluate.
• Replace B2:B100 with the range of cells containing the criteria.
• Change "YourCriteria" to the specific value you are counting.

Step 5: Test Your Formula

1. Apply filters to your dataset based on the criteria you set.
2. Check the result in the cell where you entered the formula to ensure it reflects only the visible (filtered) data.

Step 6: Troubleshooting Common Issues

• Ensure that your ranges in the formula match the data correctly.
• Verify that the Autofilter is applied and functioning as expected.
• If the result is incorrect, double-check the criteria used in the COUNTIF portion of the formula.

Conclusion

By following these steps, you can effectively use the combination of SUBTOTAL, COUNTIF, and other functions to count filtered data in Excel. This method is particularly useful for creating dynamic reports that adapt to your data filtering needs. For further practice, consider downloading the provided example file or request additional resources through the contact details shared in the video description.