Table of Contents
SUBTOTAL and SUMIF are two useful functions in Microsoft Excel that can be used together to efficiently calculate and analyze data. SUBTOTAL is a function that allows you to perform various calculations on a range of data, while also ignoring any data that may be hidden or filtered. On the other hand, SUMIF is a function that allows you to sum up a specific range of data based on certain criteria. By combining these two functions, you can easily calculate the sum of a specific range of data, while also excluding any hidden or filtered data. This can be helpful in situations where you want to analyze data based on specific criteria, while also taking into account any hidden or filtered data. Overall, using SUBTOTAL with SUMIF in Excel can help you efficiently and accurately analyze and calculate data in your spreadsheet.
Use SUBTOTAL with SUMIF in Excel
You can use the following formula to combine the SUBTOTAL and SUMIF functions in Excel:
=SUMPRODUCT(SUBTOTAL(109,OFFSET(C2,ROW(C2:C11)-ROW(C2),,1)),--(B2:B11="Guard"))
This particular formula allows you to sum the values in the range C2:C11 where the corresponding value in the range B2:B11 is 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 SUMIF 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 SUMIF() function to sum the values in the Points column where the value in the Position column is equal to “Guard”, it will actually return the sum of points for the rows equal to “Guard” in the original dataset:
Instead, we need to use the following formula:
=SUMPRODUCT(SUBTOTAL(109,OFFSET(C2,ROW(C2:C11)-ROW(C2),,1)),--(B2:B11="Guard"))
The following screenshot shows how to use this formula in practice:
This formula returns the correct sum of 85.
We can confirm this is correct by manually calculating the sum of the points values for the rows where the Position column is equal to “Guard”:
Sum of Points for Guards: 12 + 28 + 30 + 15 = 85.
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 SUMIF in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-subtotal-with-sumif-in-excel/
stats writer. "How can I use SUBTOTAL with SUMIF in Excel?." PSYCHOLOGICAL SCALES, 28 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-subtotal-with-sumif-in-excel/.
stats writer. "How can I use SUBTOTAL with SUMIF in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-subtotal-with-sumif-in-excel/.
stats writer (2024) 'How can I use SUBTOTAL with SUMIF in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-subtotal-with-sumif-in-excel/.
[1] stats writer, "How can I use SUBTOTAL with SUMIF in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use SUBTOTAL with SUMIF in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.