Hi
I have managed to get a Total count of current employees in a particular month, I would like to breakdown this count by individual Practice Areas i.e. the counts for each month in each Practice Area.
Please see attached.
Thanks
Kind regards
Cortlyn
You just add another condition, so:
=COUNTIFS(Leaving_Date,">="&I$1,Leaving_Date,"<="&EOMONTH(I$1,0),Practice_Area,I9)+COUNTIFS(Leaving_Date,"",Practice_Area,I9)
and:
=COUNTIFS(Leaving_Date,">="&I$1,Leaving_Date,"<="&EOMONTH(I$1,0),Practice_Area,I25)
HOWEVER these will return 0 in your sample data, as there are no practices assigned to any leavers in June 2014 in your data sample!!!
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
changed formula in row 2 =COUNTIFS(Leaving_Date,">="&I$1,Leaving_Date,"<="&EOMONTH(I$1,0))+COUNTIFS(Leaving_Date,"=""",$D$2:$D$4093,"<"&I$1)
in your table column Practice Area is empty for 2014
Why have you changed that formula?Originally Posted by tim201110
The continuous service data column does not come into the equation. Please see the thread in question where that part of Cortlyn's issue was dealt with: https://www.excelforum.com/excel-gen...-criteria.html
Last edited by AliGW; 07-16-2017 at 02:40 AM.
in order to count only current employees
PS seems that next conditions in COUNTIFS: "=""", "", "<>0" are different
I think tim201110 is right. The original requirement was to count the current employees. Then you need the Continuous Service Date (assuming that's the date they enter the service).
Changed the formula to use that column and also sumproduct. That's actually doing the same as countif, but with the advantage that you can evaluate parts of the formula.
If you get unexpected results then you figure out what part causes it.
Formula for calculating the headcount at start of a period and a practice.
Assumed tha Continuous Servce Date is the date entering the service.
Formula:
Please Login or Register to view this content.
Formula for calculating the leavers in a period for a practice:
Formula:Please Login or Register to view this content.
And shouldn't you also count the newcomers?
See attachment.
Cheers!
Tsjallie
--------
If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!
If you think design is an expensive waste of time, try doing without ...
Thank you very much AliGM
Thank you very much tim201110
Thank you very much Tsjallie
Your assistance is very much appreciated.
Kind regards
Cortlyn
You're welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks