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