• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

An UDF for the SUMIFS Function To Accept Array or String Comma Separated Criterias

J

JB007

New Member
I have an UDF capturing a PivotTable's Slicer's selection. Let's suppose I capture the selections of 2 slicers:
Code: 
Slicer1: A, B
Slicer2: Mon, Tue, Wed

I also have a table in an Excel worksheet that has 3 columns: Letter (many letters), Day (all days), and Sales.
I want to use an UDF that would be similar in structure to the normal Excel SumIFS formula:
Code: 
=SUMIFS_UDF(Table[Sales],Table[Letter],Slicer1,Table[Day],Slicer2)

This formula basically needs to add the results for A, B (from Letter column) and the corresponding Mon, Tue, Wed (from Day column).
 

Attachments

  • SUMIFS ARRAY CRITERIA UDF.xlsx
    9.8 KB · Views: 5
Hi ,

See the attached file.

Note that there is absolutely no error checking.

Narayan
 

Attachments

  • SUMIFS ARRAY CRITERIA UDF.xlsm
    15.9 KB · Views: 4
Hi Narayan. Thank you for this code. Two questions though.

Is there a way to make this work with a dynamic number of ranges and criterias, like in Excel's SUMIFS, because I'd like to apply this formulas sometimes having 2 range criterias, sometimes 3, sometimes 4, etc?

And can we make it work even if the ranges or criterias are hard-coded?
 
Hi ,

The code will have to be revised to take care of the second requirement.

For the first , the UDF cannot be written , as far as I know , for a dynamic number of ranges and criteria ; you can make all the ranges and criteria after the first optional , but even here , the UDF will have to provide for a specified number of ranges and criteria. I do not know if it can be absolutely dynamic.

Even the Excel worksheet function cannot take a number of ranges and criteria beyond the specified limits.

Narayan
 
Perhaps my wording ("dynamic number of ranges") was wrong, but what I mean was, I want to use the SUMIFS_UDF like I use the normal SUMIFS function, being able to specify how many CriteriaRanges and corresponding Criterias that I want, not just 2.

I believe the Excel's SUMIFS can take up to 14 criterias. That's good enough for me.
 
I understand, Narayan, thank you for letting me know.
And as I said before, it should also work with hard-coded criteria, not just cell criteria.
 
You must log in or register to reply here.
Back
Top