# Excel/VBA - How to create a function which returns a list

MJB

I am trying to create a function in Excel which returns a list. The reason behind it is:

In my workbook I am using SUMIFS. There are drop downs where user picks the criterias which are then passed to SUMIFS as arguments. To give an example let's say that I have a drop down in cell A1 and the possible values are: `"A", "B", "C", "D", "E", "F"`. My formula is:

``````=SUMIFS(sumRange,criteriaRange,A1)
``````

Assume I would like to add two more items to the drop downs: "All vowels" (so `"A"` and `"E"`) and "All consonants" (so `"B", "C", "D", "F"`). I found a way to give alternative conditions in SUMIFS by wrapping it up with SUM and providing a list of elements in SUMIFS like in the examples below:

``````=SUM(SUMIFS(sumRange,criteriaRange,{"A","E"})
=SUM(SUMIFS(sumRange,criteriaRange,{"B", "C", "D", "F"})
``````

Now I would like to substitute the lists above with a function (f) which would take the value of A1 as an argument and return: `{"A","E"}` - when A1 is "All vowels" `{"B", "C", "D", "F"}` - when A1 is "All consonants" identity in all other cases. At the end I would like the below formula to work:

``````=SUM(SUMIFS(sumRange,criteriaRange,f(A1)}
``````

for any choice in A1. Is it possible?
I tried different approaches, like returning an array, or returning a string `"{"A","E"}"`. None of those did the job.

user4039065

You just have to add an extra level of calculation. This can be done by entering your `SUM(SUMIFS(...))` as an array formula with Ctrl+Shift+Enter↵ or substituting the SUMPRODUCT function for the SUM function wrapper that totals the iterations as the SUMIFS function loops through the OR elements in the criteria.

Sample UDF:

``````Function ma(sCRIT As String)
Select Case LCase(sCRIT)
Case "all vowels"
ma = Array("A", "E")
Case "all consonants"
ma = Array("B", "C", "D", "F")
Case Else
ma = sCRIT
End Select
End Function
``````

Sample worksheet:

The array formula in F5 that returns the correct result is,

``````=SUM(SUMIFS(D1:D6, C1:C6, ma(A1)))
``````

Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered correctly, they can be copied or filled right/down to alternate locations like any other formula (subject to relative/absolute cell range addressing).

The standard formula in F6 that returns the correct result is,

``````=SUMPRODUCT(SUMIFS(D1:D6, C1:C6, ma(A1)))
``````

The SUMPRODUCT function provides that extra layer of processing that your formula requires without the use of CSE.

Both the array formula w/ CSE and the SUMPRODUCT can still use non-array, single criteria without penalty if you expand the `Select Case` of the UDF..

Collected from the Internet