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:

    UDF returning an array

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

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

TOP Ranking

HotTag

Archive