Excel: Perform a SUMIF where the criteria is a com

问题:

In my first Excel worksheet, I have a table of IDs and Values:

ID  VALUE
1   30
2   35
3   14
4   92

In my second worksheet, I will provide a comma-delimited list of IDs in one column, and in the second column, I'd like the calculated sum of those IDs' values, like this:

IDs     SUM
1,2     65
2,3,4   141
3       14

How would I write the formula for the SUM column of the second worksheet? I'd like to avoid VBA if possible.

回答1:

Assuming that, for the example tables you give:

1) Your source table is in the range Sheet1!A1:B5 (with headers in row 1)

2) Your results table is in the range Sheet2!A1:B4 (with headers in row 1)

3) The delimiter for the entries in column A of the results table is only ever a single comma

then, in Sheet2!B2, array formula**:

=SUM(IF(ISNUMBER(FIND(","&Sheet1!A$2:A$5&",",","&A2&",")),Sheet1!B$2:B$5))

Copy down to B4.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

回答2:

Consider delimiting the criteria with columns instead of commas, then you can simply add up a few Sumif formulas. Comma delimiting the criteria is a bad idea.

EDIT: Actually, why NOT use dozens of columns? Excel has plenty of them, so let's use them. Put the comma delimited list into a column. Maybe even on a different sheet. Use Text to Columns to separate the values into individual columns. Allow for a hundred. Then add a hundred columns with a single sumif formula for each column. At the very right, use a sum formula to total all sumif statements.

You can use VBA to do the Text to Columns thing and hide the columns with the split out values and the columns with the formulas, which leaves you with a column for your comma separated values next to a column with their respective totals.

回答3:

you may use User defined function to achieve this.

Function GetSum(r As String) As Long
    Dim str1, str2
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    Dim d As Long
    str1 = Split(r, ",")
    str2 = UBound(str1)
    For i = 0 To str2
        d = d + WorksheetFunction.SumIf(ws1.Range("A:A"), str1(i), ws1.Range("B:B"))
    Next i
    GetSum = d
End Function

Assuming your data in both sheet1 and sheet 2 starts with A2 then in Worksheet2 B2 apply the below formula

=getsum(A2)

回答4:

You could always hide the colums from teylyns aswer and concatenate those hidden columns in a seperate column.

OR use a formula in the hidden colums like: left(right($G2,len($G2)-2),1). The minus 2 in the right() formula should go from 0 to 2 to 4 etc as you proceed to the next column. In the G column the user could specify the IDs like "1,2,3". You will also need an if function if the len()-something is negative to use the 0 value. Maybe also a function to replace " " by "".

Hope this helps

回答5:

You can use:

=SUMPRODUCT(ISNUMBER(FIND(","&A$2:A$5&",",","&D3&","))*$B$2:$B$5)

without CTRL SHIFT ENTER