Forum Discussion
Using Excel to create count number report
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.
17 Replies
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.
- Riny_van_Eekelen
Thank you for creating the absolute solution. I have reviewed the result and best of all, can use my current software to arrive at same result. I appreciate your good work.- Glad I could help
Great solution for the older software! Well done. Once again, Excel excels in having multiple ways to go from A to B.
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.
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.
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:
- your original question "How many employees were paid each month?"
- but also, "How many months of the year did each employee work?"
In both cases, it's just a matter of counting the number of cells in the Pivot Table that were filled with a number.
- Doing that vertically tells you how many employees;
- Doing it horizontally tells you, for each employee, the number of months.
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.
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.
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.
- added a column which yield the month number (e.g., Jan becomes 1) using the formula =MONTH(date)
- then the new function UNIQUE to develop a table that consists of each unique occurrence of month and name,
- with all of that followed by a simple Pivot Table, an image of which is below. The full sheet is attached.
