Head Count by criteria

hey guys, i was wondering if someone could help me with this.
I have attached a xls workbook about the problem I am faced with. Basically, I am trying to do a employee/contractor headcount by month, by certain criteria. The problem I am having relates to the fact that excel formulas do not carry over when applying filters. In orther words, Excel formulas only perform manipulations to the source data. The second worksheet in the workbook is the Summary tab, and basically is the end result that i am looking for. The first tab is the Data Source. PLEASE HELP!

Thanks!!

-Eric

So you want a table that summarizes only what you have filtered?

See attached for setup...

Formula used in C4:

Please Login or Register  to view this content.

copied across the table

Yes, this part controls the month:

--(TEXT('Data Source'!$A$2:$A$86,"mmmm")=C$3)

wait..wats "mmmmm". as of right now, the table includes duplicate values. for example, January really only has 3 Full time employees for Department 1

That is the format used in the TEXT function to display the MONTH name extracted from a date...

So it looks at each cell in A2:A89 and converts the dates into the respective month name and then compares to then name in row 3 of your table.

I am trying to figure it out... amongst getting other things done.

Okay, try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and not just ENTER and then copy down and over the matrix.

yes in C4 of my re-organized sheet.

yes it is an array formula (confirm with CTRL+SHIFT+ENTER)

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Have a look here:

http://j-walk.com/ss/excel/eee/eee001.txt

under the Power Formula Technique.

There is another sample to download and play with here:

http://www.contextures.com/xlfunctions04.html#Visible

Yes, I think you understand it correctly.

The formula allows you to create an array of all the values in the range (as if it were unfiltering it) and assign a count of 1 or 0 to each row based on whether it was a filtered or non-filtered row... then it performs all the other functions based on all values in the list, and then it multiplies those results with each of the "subtotal" column results.. and if it multiplies by 1, it adds to the sum, if it multiplies by 0, it add 0 to the sum, so in the end, it only ends up summing/counting the items associated with your filtered list.