How Do You Subtotal with Autofilter in Excel?

excel worksheet-function autofilter
Tyler D. picture Tyler D. · Jul 27, 2011 · Viewed 7.3k times · Source

I am currently working on an Excel sheet of list of people working in a company. The company has multiple teams and multiple positions and I've listed them all in an Excel sheet (I am using Excel 2007). And because the positions and the list of people may differ on daily basis (based on who gets promoted or fired) I had to make date column and place a filter on the "date", "position", and "team".

Now I want to make it so that I filter it out for, let's say, year = 2011, position = team manager, team = information tech. team. I have managed to get this far on my own but now I am wondering when I filter this out (out of 700+ lines), based on the position or team name, if I can add the total number of people under the same team or same positions.

For example, if there are 10 people under information tech. team, can I make it so it displays "total number of people = 10", without me having to count each one of them by hand when I apply the filter? Also, if there are 4 people under information tech. team as the manager, it would show that "total number of managers under IT team = 4".

Are there any commands or methods I can use in Excel to do this?

Answer

Lance Roberts picture Lance Roberts · Jul 27, 2011

Enter the following in the cell you want it in, and it will change, as per this link:

=SUBTOTAL(3, C2:C1000)

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Get values from other sheet using VBA

I want to get values from other sheets. I have some values in Excel (sheet2) for example: A B C D - - - - 1 | 2 5 9 12 2 | 5 8 4 5 3 | 3 1 2 6 I sum each column in row 4. I'm working with these values in sheet2 but I …

Read More
Excel formula to get cell color

I would like to know if we can find out the Color of the CELL with the help of any inline formula (without using any macros) I'm using Home User Office package 2010.

Read More