Using Excel to create count number report

Occasional Contributor

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

Re: Using Excel to create count number report

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.

Re: Using Excel to create count number report

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.

Re: Using Excel to create count number report

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.

Re: Using Excel to create count number report

He is using Excel 2007.

Re: Using Excel to create count number report

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.

Re: Using Excel to create count number report

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

Re: Using Excel to create count number report

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.

Re: Using Excel to create count number report

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

Re: Using Excel to create count number report

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.

Re: Using Excel to create count number report

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

Re: Using Excel to create count number report

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.

Re: Using Excel to create count number report

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))}``

Re: Using Excel to create count number report

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

Re: Using Excel to create count number report

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.

Re: Using Excel to create count number report

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

Re: Using Excel to create count number report

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.