How to Calculate Attendance Percentage from Two Sums in Excel
Question details
The user wants to find a cumulative attendance percentage by dividing a sum of actual attendance by a sum of total possible attendance.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking and calculating cumulative attendance records where the final ratio needs to be displayed as a correct percentage.
- Observed behavior
- The goal is to calculate the division of two summed ranges and correctly format the resulting decimal as a percentage with specific decimal places, rather than manually multiplying the result by 100.
Ensure your attendance data is organized properly, with actual attendance counts in one column or row, and the total possible counts in another.
Divide Sums and Apply Percentage Formatting
Use a formula to divide the sum of actual attendance by the sum of possible attendance, then use Excel's native cell formatting to display it as a percentage.
Instead of mathematically multiplying your division result by 100, the best practice in Excel is to calculate the raw decimal ratio and apply a Percentage number format. This keeps the underlying data accurate for future calculations.
Select an empty cell for your result and type the formula =SUM(B2:B5)/SUM(C2:C5), replacing the cell references with the actual ranges for your attendance data and total possible counts.
Press Enter to execute the formula. Excel will return a decimal value, such as 0.941176.
Click on the result cell to select it. Navigate to the Home tab on the ribbon and click the Percentage Style (%) icon located in the Number group.
To specify the accuracy (like 94.12%), click the 'Increase Decimal' or 'Decrease Decimal' buttons next to the Percentage icon on the Home tab until you reach two decimal places.

Calculate Percentages Easily with WPS Spreadsheet
WPS Office offers a fully compatible and intuitive spreadsheet application that makes complex formula calculations and percentage formatting incredibly straightforward.
- 1. Open your data: Launch WPS Spreadsheet and open your attendance tracking workbook.
- 2. Input the division formula: Select the target cell and type =SUM(Range1)/SUM(Range2), using your actual data ranges.
- 3. Execute calculation: Press Enter on your keyboard to reveal the decimal calculation result.
- 4. Format instantly: Go to the Home tab and click the '%' icon to format the decimal as a percentage instantly.

Frequently Asked Questions
Why is my attendance percentage showing as a decimal instead of a percentage?
By default, dividing two numbers in Excel produces a raw decimal (e.g., 0.94 instead of 94%). You must explicitly format the cell as a Percentage from the Home tab to display it with a percent sign, which eliminates the need to multiply your formula by 100.
How do I copy the formula without shifting my total ranges?
You need to use absolute references. Add dollar signs to your cell range coordinates (for example, =SUM($B$2:$B$5)/SUM($C$2:$C$5)). This locks the specified cells so they do not shift relative to the new location when you drag the formula down or across.
Can I calculate the sum if some attendance cells are blank?
Yes, the SUM function automatically ignores blank cells and text strings when adding numbers. However, ensure that the divisor (your total possible count) evaluates to a number greater than zero, otherwise Excel will return a #DIV/0! error.




