Sep 15 2021 11:19 AM
I use Excel 2007 on Windows 10. I have a database of employee pay for 12 months. I am trying to produce a report showing how many employees were paid each month.
Sep 15 2021 11:49 AM
There are several ways to do that
Pivot table, Formulas, Power Query, etc.
I'm attaching 2 different solutions so you can choose witch one is best for you.
Sep 15 2021 12:05 PM
Sadly, a straight pivot table doesn't answer the question. I tried that too. The original question was "how many employees were paid each month?" There were indeed 124 payments in January, but only 61 employees receiving those 124 payments.
I was stymied by this one.....still haven't figured it out.
Sheet2 in the attached shows the detail behind the Pivot Table's count for Jan.
Sep 15 2021 12:49 PM - edited Sep 15 2021 12:50 PM
Two intervening steps, partly for sake of repeatability. There may well be more efficient ways, using Power Query or the like (which I don't have on the Mac), but this is for clarity and repeatability.
Sep 15 2021 09:24 PM
Are you able to update your software to the most recent version of Excel? The UNIQUE function that I used--as well as many other new features that are quite powerful--are simply not available on your edition, as @Juliano-Petrukio has correctly pointed out.
Sep 15 2021 10:43 PM
@levicnhotmailcom Since both modern functions and Power Query are not available for your Excel version. A regular pivot table counting the number of payments per person per month can be the basis for counting the number of cells with values each month. A working example in the attached xls file is attached. It should work in your version.
Sep 16 2021 07:21 AM
Yes, I have considered updating my software version, but do not buy into subscription as a way towards achieving the goal. I prefer to buy a software and use it the best way I see fit. The current subscription method by Microsoft has forced many people I may say, including myself to continue using old non-supported software.
You did a marvelous job creating those result. I wished I had the updated software to recreate it. Thanks for the superlative effort.
Sep 16 2021 07:49 AM
Great solution for the older software! Well done. Once again, Excel excels in having multiple ways to go from A to B.
Sep 16 2021 08:01 AM
Inspired by the solution offered by @Riny_van_Eekelen, and totally building off his initial results, I created the attached sheet, showing how his initial pivot table (which you can readily create with last year's or the current partial year's actual data) can be used to answer two questions:
In both cases, it's just a matter of counting the number of cells in the Pivot Table that were filled with a number.
Sep 16 2021 08:47 AM
Sep 20 2021 10:40 AM
Sep 20 2021 04:42 PM - edited Sep 20 2021 04:56 PM
Well, I'm not a big fan of auxiliar columns nor complex matrices formulas because with a huge dataset it jeopardises the performance.
Anyway, find attached 2 possible solutions you can try them.
{=SUM(IF((MONTH(DateColumn)=MonthID)*MATCH(MONTH(DateColumn)&EmployeeColumn;MONTH(DateColumn)&EmployeeColumn;0)=MATCH(ROW(EmployeeColumn);ROW(EmployeeColumn));1;0))}
Sep 20 2021 05:32 PM
Sep 20 2021 06:30 PM
There is no column named as MONTH.
There is a report where you can see month by month the result of the payments (quantity and summation)
When we put a date on a column we can manage it with some different date formulas such as Month(Date).
Have a look, I posted a file with the same data provided by you.
Sep 20 2021 08:36 PM
I am totally lost on how you used the date to produce "Month" column under the PivotTable Field List. I believe this goes to your statement that "When we put a date on a column we can manage it with some different date formulas such as Month(Date)".
Sep 21 2021 02:53 AM - edited Sep 21 2021 02:54 AM
Excel auto identifies that the field is a date type field and then group it in:
Years > Quarters > Months > Full date.
I'm not sure if Excel 2007 version does that, but take a look on this article
Ps.: Dont forget to hit the like button and mark as solved if its solved.