• 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

larryf

New Member
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?
 

p45cal

Well-Known Member
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)
 

Peter Bartholomew

Well-Known Member
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

larryf

New Member
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.
 
Top