Function Problems

How to Use SUMIF to Total Duplicate Attendance Entries in Excel

John WilsonJohn Wilson Oct 28, 2026 869 views

Question details

The user needs to calculate the total attendance for individuals who appear multiple times in a dataset.

How to Use SUMIF to Total Duplicate Attendance Entries in Excel
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.
Before you start

Ensure your dataset is organized with names in one column and their corresponding attendance values in another column.

Solution 1Recommended

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.

1
Select the target cell

Click on the cell where you want the total attendance for a specific person to appear (e.g., cell F2).

2
Enter the SUMIF formula

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.

3
Apply the formula

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 the SUMIF Function to Total Duplicate Entries
Absolute References: Using absolute references (the $ signs) ensures that your data ranges do not shift when you copy the formula to other cells.
Calculate Data Easily with WPS Spreadsheet

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. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your attendance tracking workbook.
  2. 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. 3. Press Enter: Hit Enter to get the accurate total for duplicate attendance entries instantly.
Fully compatible with Microsoft Excel (.xlsx) file formats.Supports standard formulas like SUMIF, COUNTIF, and VLOOKUP.Lightweight and fast, even with large datasets.Free to use with a familiar, user-friendly interface.
microsoft office alternative - wps office

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.