Memuat
Memuat
SUMIFS and COUNTIFS Summary for employee attendance and payroll
0 views
Sums, counts, and averages data per category from the Data sheet. Results appear on the Rekap sheet.

A complete, tidy per-category summary with no pivot needed.
The real project file is available. It is not just the picture above: you get the working file with every formula in place. It can be downloaded after payment, and we send the download link by WhatsApp or email.
.xlsx workbook (.xlsx)
| Cell | Formula | What it does |
|---|---|---|
| H2 | =SUMIFS(Data!$G:$G,Data!$C:$C,C2) | Total value for the category on this row. |
| I2 | =SUMIFS(Data!$D:$D,Data!$C:$C,C2) | Total qty for the category on this row. |
| J2 | =COUNTIFS(Data!$C:$C,C2,Data!$F:$F,F2) | Transactions with the same category and status. |
| K2 | =IFERROR(AVERAGEIFS(Data!$G:$G,Data!$C:$C,C2),0) | Average value for this category. |
Columns of the Data sheet:
| Pay Component | Amount |
|---|---|
| Staff Base Salary | 4,500,000 |
| Supervisor Base Salary | 6,500,000 |
| Transport Allowance | 300,000 |
| Daily Meal Allowance | 25,000 |
| Overtime per Hour | 30,000 |
| Target Bonus | 750,000 |
| BPJS Health Contribution | 120,000 |
| BPJS Employment Contribution | 95,000 |
| Late Deduction | 20,000 |
| Holiday Allowance | 3,000,000 |
The sample data is synthetic.
Yes. The picture above is only a preview. After payment we send the download link of the real project file (an .xlsx workbook) by WhatsApp or email.
No. The sample data is synthetic and only shows how the formulas behave. Replace it with your own data and the formulas keep working.
Yes, as a separate order. Message us on WhatsApp with a short description of your columns and what you want to see.
Sample data is synthetic. Google Sheets is a trademark of Google LLC.
Back to Google Sheets portfolio