Table of Contents
SUBTOTAL and COUNTIF are two useful functions in Excel that can be used together to efficiently analyze data. SUBTOTAL allows users to calculate a subtotal for a selected range of cells, while COUNTIF counts the number of cells in a range that meet a specific criteria. By combining these two functions, one can easily obtain a subtotal of the number of cells that meet a certain condition. This is particularly useful for analyzing large datasets and obtaining specific insights. To use SUBTOTAL with COUNTIF, simply select the range of cells you want to analyze, choose the appropriate SUBTOTAL function, and specify the COUNTIF function as the first argument. This will allow you to get a subtotal of the number of cells that meet the specified criteria, providing a quick and efficient way to analyze your data in Excel.
Use SUBTOTAL with COUNTIF in Excel
You can use the following formula to combine the SUBTOTAL and COUNTIF functions in Excel:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="Guard"))
This particular formula allows you to count the number of cells in the range B2:B11 equal to “Guard” even after that range of cells has been filtered in some way.
The following example shows how to use this formula in practice.
Example: How to Use SUBTOTAL with COUNTIF in Excel
Suppose we have the following dataset that contains information about various basketball players:
Next, let’s filter the data to only show the rows where the players are in the West conference.
To do so, highlight the cell range A1:C11. Then click the Data tab along the top ribbon and click the Filter button.
Then click the dropdown arrow next to Conference and make sure that only the box next to West is checked, then click OK:
The data will automatically be filtered to only show the rows where the Conference column is equal to West:
If we attempt to use the COUNTIF() function to count the number of rows where Position is equal to “Guard”, it will actually return the count of the rows equal to “Guard” in the original dataset:
Instead, we need to use the following formula:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="Guard"))
The following screenshot shows how to use this formula in practice:
This function only counts the number of visible rows where Position is equal to “Guard”, which happens to be 4 rows.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can I use SUBTOTAL with COUNTIF in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-subtotal-with-countif-in-excel/
stats writer. "How can I use SUBTOTAL with COUNTIF in Excel?." PSYCHOLOGICAL SCALES, 28 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-subtotal-with-countif-in-excel/.
stats writer. "How can I use SUBTOTAL with COUNTIF in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-subtotal-with-countif-in-excel/.
stats writer (2024) 'How can I use SUBTOTAL with COUNTIF in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-subtotal-with-countif-in-excel/.
[1] stats writer, "How can I use SUBTOTAL with COUNTIF in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use SUBTOTAL with COUNTIF in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.