Excel求和公式优化：如何避免重复统计同一居民代码的入住天数？

Got it, let's tackle this duplicate counting issue you're facing with your occupancy days formula. The problem with your current setup is that it only handles consecutive duplicate resident codes by comparing each row to the next one—if a resident's records are split by other entries, it ends up counting their days multiple times. Here's how to fix it, depending on your Excel version and exact needs:

Option 1: For Excel 365/2021 (Dynamic Array Support) #

This version uses modern functions for cleaner, more intuitive logic. It will calculate the total occupied days for each unique resident (summing all their records if they have multiple) and avoid duplicates entirely:

=IF(Z12="E",Y15,SUM(SUMIFS(ExpectedFeesTbl[OCCUPIED DAYS16],ExpectedFeesTbl[Home Code],HomeCodePL1,ExpectedFeesTbl[Resident code],UNIQUE(FILTER(ExpectedFeesTbl[Resident code],ExpectedFeesTbl[Home Code]=HomeCodePL1)))))

How it works:

1. FILTER grabs all resident codes that match your target HomeCodePL1
2. UNIQUE strips out duplicates from that list, leaving only one entry per resident
3. SUMIFS calculates the total occupied days for each unique resident under the matching home code
4. The outer SUM adds up all those individual totals to get your final count

Option 2: For Older Excel Versions (No Dynamic Arrays) #

If you're using an Excel version without dynamic array support, use this SUMPRODUCT-based formula to achieve the same result:

=IF(Z12="E",Y15,SUMPRODUCT((ExpectedFeesTbl[Home Code]=HomeCodePL1)/COUNTIFS(ExpectedFeesTbl[Resident code],ExpectedFeesTbl[Resident code],ExpectedFeesTbl[Home Code],ExpectedFeesTbl[Home Code])*ExpectedFeesTbl[OCCUPIED DAYS16]))

How it works:

1. The (ExpectedFeesTbl[Home Code]=HomeCodePL1) check filters rows to only your target home code
2. COUNTIFS calculates how many times each resident code appears under the same home code
3. Dividing by that count gives each duplicate record a weight of 1/number of duplicates—so summing those weighted values for a resident equals their total occupied days (no double-counting)
4. SUMPRODUCT combines all these calculations into a single total

If You Need to Count Only One Record per Resident (e.g., First/Last Entry) #

If your goal isn't to sum all a resident's days, but rather to count only their first or last recorded entry:

• First entry only (Excel 365):

  =IF(Z12="E",Y15,SUM(XLOOKUP(UNIQUE(FILTER(ExpectedFeesTbl[Resident code],ExpectedFeesTbl[Home Code]=HomeCodePL1)),ExpectedFeesTbl[Resident code],ExpectedFeesTbl[OCCUPIED DAYS16],"",0,1)))
  

• Last entry only (Excel 365):

  =IF(Z12="E",Y15,SUM(XLOOKUP(UNIQUE(FILTER(ExpectedFeesTbl[Resident code],ExpectedFeesTbl[Home Code]=HomeCodePL1)),ExpectedFeesTbl[Resident code],ExpectedFeesTbl[OCCUPIED DAYS16],"",0,-1)))
  

内容的提问来源于stack exchange，提问作者Robert Hall