Forum Discussion
Please Help with complex conditional formula to determine headcounts per each month
In N2:
=($J2<EOMONTH(N$1,0))*(IF($G2<>"",$G2,IF($K2<>"",$K2,DATE(2099,12,31)))>=N$1)*($I2<>"not EHQ HC")*($I2<>"EXTERNAL")
Fill down, then to the right, or vice versa.
Hello Hans Vogelaar,
Many thanks for your feedback and it indeed was PRECISELY what I needed. Thanks so much.
I have used it successfully and in the sheet "HR_Dashboard Report" in the updated attached file, it works completely fine.
I have tried to create a pivot table with it but what I am noticing is that if I double-click the grand total value amount Excel spits out all source data rows, completely disregarding the filter setting.
For example, looking at the pivot table on the "Pivottable" sheet, when i double click on cell B13 it should normally show me only 6 rows but this is not the case(with headers). Any idea what I could be doing wrong here?
Thanks.
NoviceKay
I'm afraid that's how pivot tables work - all rows contribute to a total, even if that contribution is 0...
- I understand what you mean but I know from previous pivot tables(for other reports) that normally when I click on the number in the pivot table it will show me EXACTLY the data the number is representing. For instance if I click on a 2, it will open a new sheet with 2 rows of my original data and this is what I am not getting with this exact pivot table.
Hope you got my point HansVogelaar. Sorry for the bother 🙂You will get fewer rows if you double-click one of the numbers in the interior of the pivot table.
You are right. When I click on cell B4 in the pivot table for "Black" Department (Jan2020), i would expect it to show me just 2 rows but it shows 4 rows including the row for Miss Rene Bruen who started in October 2021.
I struggle to see how that row contributed to the number 2 in cell B4.