Hello:
Please refer to attached sheet.
I have payroll info as shown.
I need formula in cell H2 to S2 to give unique employees for given month.
Please let me know if you have any questions.
Thanks.
Riz
Hello:
Please refer to attached sheet.
I have payroll info as shown.
I need formula in cell H2 to S2 to give unique employees for given month.
Please let me know if you have any questions.
Thanks.
Riz
Hi - you want to count the number of employees in each month? 36 in Jan, 43 in Feb,etc.
Please select try at H2
=COUNT(1/FREQUENCY(IF($A$2:$A$410>=--(1&H1),IF($A$2:$A$410<EDATE(1&H1,1),MATCH($B$2:$B$410,$B$2:$B$410,))),ROW($A$2:$E$410)-ROW($A$1)))
or
=COUNT(1/FREQUENCY(IF($D$2:$D$410=H1,MATCH($B$2:$B$410,$B$2:$B$410,)),ROW($A$2:$E$410)-ROW($A$1)))
Confirm by Ctrl+Shift+Enter
or Normal Enter but calculate slower than FREQUENCY
select H2:S2
enter formula
=SUMPRODUCT((Table1[Month]=H1)/COUNTIFS(Table1[Name],Table1[Name],Table1[Month],Table1[Month]))
Press Ctrl+Enter
Last edited by Bo_Ry; 10-07-2020 at 03:05 PM.
Hello Bo_Ry
Very complicated formula but it works
Thanks a lot
Roz
Riz,
Power Query Solution
Excel 2016 (Windows) 32 bitPlease Login or Register to view this content.
A B 1Month Total By Month 2Jan 23 3Feb 23 4Mar 25 5Apr 23 6May 25 7Jun 26 8Jul 26 9Aug 24 10Sep 23
Sheet: Sheet2
Last edited by alansidman; 10-07-2020 at 05:35 PM.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks