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.
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.
- He is using Excel 2007.
