Sep 15 2021 11:19 AM
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
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 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 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.
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