2

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.

Community
  • 1
  • 1
Nick Criswell
  • 1,733
  • 2
  • 16
  • 32
  • 1
    The `{"Dog","Cat"}` string is a _string_, not an array. Passing a string, even if it resembles an array, is still a string not an array, so won't work. You say you have a VBA function that returns a comma separated list into a cell. Try changing that to return an array, and calling it in your `SUMIFS` formula (bypassing returning the list to a cell) – chris neilsen Feb 08 '17 at 23:28
  • Thanks @chrisneilsen. The function I'm using is a lot like the one in example 2 of the answer listed [here](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops). I tried to follow the example [here](http://stackoverflow.com/questions/1815980/excel-vba-function-returning-an-array) to return a collection but I am getting an error for 'argument not optional' on my output. Any thoughts on how to make sure my function returns the array as desired? – Nick Criswell Feb 08 '17 at 23:46
  • If you post the code and a description of what you want the function to de I can help – chris neilsen Feb 08 '17 at 23:48

1 Answers1

1

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)))
A.S.H
  • 29,101
  • 5
  • 23
  • 50
  • 1
    I was just thinking after my comment to @chris neilson, "Hey, why don't I just write a VBA function to split a cell string into an array!?" You read my mind. Thank you! This works splendidly. – Nick Criswell Feb 08 '17 at 23:53
  • @NickCriswell That's great. Glad to know it helped :) – A.S.H Feb 08 '17 at 23:56
  • 1
    By the way, this answer granted me the moderator tools, thank you!! – A.S.H Feb 08 '17 at 23:57
  • 1
    @NickCriswell Unless you have other uses for the csv list, I still think returning an array rather than a csv list is a better solution – chris neilsen Feb 09 '17 at 00:00
  • 1
    @chrisneilsen, yes, I am going to modify my code so it does the string split before the return-eventually. Your comments and the answer certainly pointed me in the right direction on this one. Appreciate the help. – Nick Criswell Feb 09 '17 at 00:04
  • Any idea why this would be returning the SUMIF only subject to the first criteria in the CSV/array? This seems to be occurring. – Nick Criswell Feb 09 '17 at 17:26
  • I found the solution to the question I was asking. Once you use the `splitCSV` function, this all needs to be returned as a Ctrol+Shift+Enter formula rather than a regular spreadsheet function. Guess I should have known that. Thanks! – Nick Criswell Feb 09 '17 at 17:37
  • 1
    @NickCriswell sorry for not mentioning it before. Anyway, indeed, you can have it as a normal (non-array) formula by replacing `SUM` with `SUMPRODUCT`. The latter when fed a single array will act just like the former but without needing to be an array formula. It's a fantastic tool in this kind of situations. – A.S.H Feb 09 '17 at 19:36
  • 1
    @A.S.H, thanks for the heads up on the `SUMPRODUCT` switch. This seems to be improving the performance compared to the CSE function with `SUM`. – Nick Criswell Feb 09 '17 at 20:16
  • Yes I expect that, because instead of first computing an array and then summing it up, it multiplies and sums in one go. That's my proper opinion though, it is not an admitted fact. – A.S.H Feb 09 '17 at 20:24
  • 1
    I feel that I have to edit the answer to use `SUMPRODUCT`. That should be the fully correct answer I think. – A.S.H Feb 09 '17 at 20:27