Using Excel to create count number report

Copper Contributor

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 

 

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.

 

JulianoPetrukio_0-1631731713954.png

 

@Juliano-Petrukio 

 

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.

@levicnhotmailcom 

 

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.

mathetes_0-1631735131091.png

 

He is using Excel 2007.

@levicnhotmailcom 

 

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.

@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.

@mathetes 

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. 

@Riny_van_Eekelen 

 

Great solution for the older software! Well done. Once again, Excel excels in having multiple ways to go from A to B.

@levicnhotmailcom 

 

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.
@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
I ran into a glitch while trying to re-create your solution to the count problem with the Pivot table. How you linked the sum of values to the Names, and how you created the months of the year. Everything I did mirrored your output.

@levicnhotmailcom 

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.

 

JulianoPetrukio_0-1632182077923.png

 

 

 

 

{=SUM(IF((MONTH(DateColumn)=MonthID)*MATCH(MONTH(DateColumn)&EmployeeColumn;MONTH(DateColumn)&EmployeeColumn;0)=MATCH(ROW(EmployeeColumn);ROW(EmployeeColumn));1;0))}

 

 

 

 

 

 

I still couldn't make out how the "month" column came in place. It is not in the data sheet.

@levicnhotmailcom 

 

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.

@Juliano-Petrukio  

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)".

 

@levicnhotmailcom 

 

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.