Hi,
I'm trying to get the SUMPRODUCT formula to calculate month over month headcount. I can get the first month, but if someone is terminated, it continues to add everyone, even those that were terminated. I've included what I'm hoping the final product can look like. Any and all help is welcome!
Thanks in advance!
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Posting a small file will help in formulating a possible solution.
Attachment would help.First time user right here!
In E2
=SUMPRODUCT(($A$2:$A$22<=E$1)*(($B$2:$B$22="")+($B$2:$B$22>E$1)))
Copy across
Life's a spreadsheet, Excel!
Say thanks, Click *
Thanks, Ace! This worked.
Hi,
I have a similar problem with headcount and to collate monthly headcount but the problem here is I have a dataset with employee ids, start date, end date. So we need to use the same dataset and apply the formula in a manner to get in from of each employee a headcount that when using a pivot gives us average headcount per month using average headcount from previous month + new hires - leavers. For example, for Jan the average headcount could be computed using average dec headcount + new joinees in Jan and leavers in Jan.
Can anyone please suggest how can this be achieved.
Thanks,
Ridhima
The dataset looks like:
Employee id Start date end date HEADCOUNT
12345 12/30/2019 2/1/2020
21345 1/2/2020 1/20/2020
31245 12/31/2019
41235 2/2/2020
54321 12/31/2019
13234 6/3/2020
14345 4/2/2020 7/5/2020
15435 12/31/2019 6/30/2020
12543 12/31/2019
11334 12/31/2019
11223 4/2/2020
11334 7/5/2020 7/31/2020
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks