• 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.

Multiple Optional Selection Criteria in Sumifs

larryf

New Member
Hi Friends,

I'm creating a report-generator that is based on data in a sheet that has 50+ columns. My end users are going to want to see various versions of this report using the values in 4 of the columns as selection criteria. For each of the 4 columns in question, they will want to select either an individual value as the criteria OR not use that column to select on. I need to calculate sums, counts, etc. for some of the other columns on the basis of the selection criteria in the 4 key columns.

Please see the attached example file. I'm using drop-down data validation (orange cells) for the users to select values for the 4 key columns, and also allowing 'All' as a choice for each to not use that particular column to select on. In the example, I'm looking for a sum of the 'Cost' column for all records that meet the criteria the user selects.

I have no problem when the selection criteria are each one of the values in the 4 key columns. I'm hamstrung, however, if any of the criteria are excluded (user selects 'All'). Can you help me with a formula that takes 'All' into account? Thanks in advance for your help!
 

Attachments

  • Multiple Condition (w All) selection criteria.xlsx
    11 KB · Views: 7
A simple PIVOT TABLE with slicers can also do the trick. I'll leave it up to you to adapt to your needs
 

Attachments

  • Copy of Multiple Condition (w All) selection criteria.xlsx
    18.6 KB · Views: 8
Thanks for your reply, pecoflyer! My end users seem to delight in not understanding pivot tables or slicers. Is there a way to do this formulaically so I can lock down the workbook except for the data validation dropdowns?
 
Code:
=SUMPRODUCT(IF(C2="All",ROW(A8:A17)>0,A8:A17=C2)*IF(C3="All",ROW(B8:B17)>0,B8:B17=C3)*IF(C4="All",ROW(C8:C17)>0,C8:C17=C4)*IF(C5="All",ROW(D8:D17)>0,D8:D17=C5),E8:E17)
 
As a 365 user, other solutions are available
76746

With insider beta, it is possible to use the Lambda function BYROW to avoid the mathematical trickery of MMULT
Code:
= LET(
      selected,
         BYROW(Data,
            LAMBDA(record,
               AND((record=filters)+(filters="All")))),
      SUM( FILTER(Table1[Cost], selected, 0) ) )
but at the expense of more advanced programming.
 

Attachments

  • Multiple Condition (w All) selection criteria.xlsx
    13.6 KB · Views: 6
Thank you, p45cal and Peter! :) All three of your solutions appear to work perfectly (though no lambdas on my production machine--yet). I appreciate your help! The best I'd managed on my own was a fragile hack of a formula.
 
Back
Top