Excel：基于共同索引对列求和的实现方案咨询

Since your SUMIF approach struggled with non-aligned rows, here are three reliable methods to get the sum of 2017 values where their DayIndex matches any DayIndex from 2018:

Method 1: SUMPRODUCT + COUNTIF (Works in all Excel versions) #

This method checks each 2017 DayIndex to see if it exists in the 2018 DayIndex range, then sums the corresponding values.

Assuming your ranges are:

• DayIndex2018: A2:A5 (values 1,2,3,4)
• DayIndex2017: C1:C5 (values 1-5)
• Value2017: D1:D5 (values 20,45,55,33,23)

Use this formula:

=SUMPRODUCT(D1:D5, --(COUNTIF(A2:A5, C1:C5) > 0))

How it works: #

1. COUNTIF(A2:A5, C1:C5) counts how many times each 2017 DayIndex appears in the 2018 range. For matching indices, this returns 1; for non-matching, 0.
2. --(...) converts the boolean result (TRUE/FALSE from >0) to numeric values (1/0).
3. SUMPRODUCT multiplies each Value2017 by its corresponding 1 or 0, then sums the results—only matching values are included.

Method 2: SUM + VLOOKUP (Excel 365 or Array Formula for older versions) #

This approach looks up each 2018 DayIndex in the 2017 data and sums the resulting values.

For Excel 365 (dynamic arrays): #

=SUM(VLOOKUP(A2:A5, C1:D5, 2, FALSE))

For older Excel versions (enter with Ctrl+Shift+Enter): #

=SUM(VLOOKUP(A2:A5, C1:D5, 2, FALSE))

How it works: #

1. VLOOKUP(A2:A5, C1:D5, 2, FALSE) retrieves the Value2017 for each DayIndex2018 (since we're looking up exactly matching indices).
2. SUM adds all the retrieved values together, giving you the total of 20+45+55+33=153.

Method 3: SUM + IF + MATCH (Array Formula for older Excel) #

This uses MATCH to check for existing indices, then sums the relevant values.

Enter with Ctrl+Shift+Enter (not needed in Excel 365):

=SUM(IF(ISNUMBER(MATCH(C1:C5, A2:A5, 0)), D1:D5, 0))

How it works: #

1. MATCH(C1:C5, A2:A5, 0) returns the position of each 2017 DayIndex in the 2018 range (or #N/A if not found).
2. ISNUMBER converts these results to TRUE (for matches) or FALSE (for non-matches).
3. IF returns Value2017 for TRUE results, and 0 for FALSE.
4. SUM adds all these values to get your total.

All three methods will give you the desired sum of 153, regardless of the row alignment between the two index columns.

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