Search code examples
excelvbasumifs

Excel SUMIFS Multiple Criteria Cell Value

Similar questions to this have been asked but not exactly like this. There is one that is very close, but that solution is not working for me.

I have a function which returns comma separated values into a cell. I would like to pass the elements in that cell as criteria to a SUMIFS function using an approach like this one.

My attempt is pictured below:

enter image description here

I believe that this is somehow tied to the way that the function is understanding what is in cell G8. It looks like it is adding some extra quotes. If I highlight G8 in the formula bar and press F9, I get:

enter image description here

There are extra quotes on each side of each criteria.

I am open to a custom VBA function solution, but I would prefer something which can be built as a worksheet function. The criteria are returned from a custom VBA function that pulls elements out of a list box and does some regex work to remove extra commas. The number of elements that can be selected is variable so I would like to avoid having to split the criteria into more than one cell. Thanks.

Solution

  • Seems that the raw comma-separated criteria is in G6, All you need is to split this criteria into an array and feed it to SUMIFS. Splitting is available in VBA, but not exposed to Excel. All we need is to write a little UDF that does the splitting of the CSV and use it in our formula:

    Function splitCSV(str As String)
    splitCSV = Split(str, ",")
End Function

    Now the formula in F10 would be:

    =SUM(SUMIFS(C3:C10, B3:B10, "blue", A3:A10, splitCSV(G6)))

    EDIT

    The above is an array formula (Ctrl+Shift+Enter). To have it a normal formula we can use SUMPRODUCT instead of SUM. This leads to more flexibility (normal formula vs array formula) as well as some "expected" performance improvement.

    =SUMPRODUCT(SUMIFS(C3:C10, B3:B10, "blue", A3:A10, splitCSV(G6)))