How to Use SUMIF to Total Duplicate Attendance Entries in Excel
Question details
The user needs to calculate the total attendance for individuals who appear multiple times in a dataset.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating total attendance for each person across multiple positions where names are duplicated in the list.
- Observed behavior
- The current formula only counts unique entries, resulting in an incorrect total attendance calculation.
Ensure your dataset is organized with names in one column and their corresponding attendance values in another column.
Use the SUMIF Function to Total Duplicate Entries
The SUMIF function is the most reliable way to sum values based on a specific condition, such as a person's name, and works perfectly in all Excel versions including Excel 2016.
Click on the cell where you want the total attendance for a specific person to appear (e.g., cell F2).
Type the formula =SUMIF($B$2:$B$50, E2, $C$2:$C$50). In this formula, $B$2:$B$50 is the range containing the names, E2 is the specific name you are looking for, and $C$2:$C$50 is the range containing the attendance values.
Press Enter to calculate the total. You can then drag the fill handle down to apply this formula for other names in your summary list.

Use UNIQUE and FILTER Functions (Excel 365 / 2021 and later)
If you are using a newer version of Excel, you can dynamically extract unique names and calculate their totals using modern dynamic array functions.
Easily Total Duplicate Entries in WPS Spreadsheet
WPS Spreadsheet provides robust support for all standard formulas, including SUMIF, allowing you to easily calculate attendance totals for duplicate entries. It is fully compatible with Microsoft Excel formulas and file formats.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your attendance tracking workbook.
- 2. Input the SUMIF formula: Select your result cell and enter the identical formula =SUMIF($B$2:$B$50, E2, $C$2:$C$50).
- 3. Press Enter: Hit Enter to get the accurate total for duplicate attendance entries instantly.

Frequently Asked Questions
Why does my formula only count unique entries instead of summing them?
If you are getting a count rather than a sum, you might be using functions like COUNT, COUNTA, or counting unique filtered values instead of a summation function. To add up the actual attendance numbers, you must use the SUMIF function.
Can I use the UNIQUE function in Excel 2016?
No, the UNIQUE function is a dynamic array function introduced in Excel 365 and Excel 2021. For Excel 2016 and older, you should manually create a list of unique names or use the Remove Duplicates feature in the Data tab before applying the SUMIF formula.
What do the dollar signs ($) mean in the SUMIF formula?
The dollar signs create an absolute reference (e.g., $B$2:$B$50). This locks the range so that when you copy and paste the formula down a column for other names, the referenced data range remains constant and does not shift.




