Excel按行与多列求和：特定日期下标签15、8列的批量求和方法

Let's break down how to get this done efficiently, even with your 50K+ columns:

Method 1: Using INDEX + MATCH + SUM (Most Efficient for Single Date) #

This method directly targets the exact cells you need, avoiding unnecessary calculations across the entire dataset—perfect for large spreadsheets.

Assumptions: #

• Dates are stored in column A (one unique date per row)
• Column labels (8, 15, etc.) are in row 1
• Your dataset spans from column A to XFD (Excel’s maximum column limit)

Formula: #

=SUM(INDEX(A:XFD, MATCH("2024-05-20", A:A, 0), MATCH({8, 15}, 1:1, 0)))

How it works: #

1. MATCH("2024-05-20", A:A, 0) finds the row number where your target date is located (replace the date with your specific value or a cell reference like B1).
2. MATCH({8, 15}, 1:1, 0) returns the column numbers for labels 8 and 15.
3. INDEX(...) pulls the two cells at the intersection of the target row and columns.
4. SUM() adds those two values together.

Quick Note: If your column labels are formatted as text (not numbers), adjust the match array to {"8", "15"}.

Method 2: Using SUMPRODUCT (Flexible for Multiple Conditions) #

If you ever need to expand to multiple dates or more complex criteria, SUMPRODUCT handles cross-conditional sums seamlessly.

Formula: #

=SUMPRODUCT((A:A="2024-05-20")*((1:1=8)+(1:1=15)), A:XFD)

How it works: #

1. (A:A="2024-05-20") checks which rows match your target date (Excel treats TRUE/FALSE as 1/0 for calculations).
2. ((1:1=8)+(1:1=15)) checks which columns are labeled 8 or 15 (the + acts as an OR condition).
3. Multiplying these two arrays creates a filter: only cells matching both the date row and label columns are included.
4. SUMPRODUCT sums all the filtered cells in your dataset range.

Key Tips for Large Datasets: #

• INDEX + MATCH is faster than SUMPRODUCT because it directly locates cells instead of iterating through every cell in the range.
• Ensure date values are consistent (no text-formatted dates mixed with actual date values—use DATEVALUE() if needed to convert text to dates).
• If you have duplicate column labels or dates:
  • INDEX + MATCH will only pull the first match.
  • SUMPRODUCT will sum all matching cells (useful if duplicates are intentional).

内容的提问来源于stack exchange，提问作者Mutum Pamel