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

Returning data with Check Boxes that are not mutually exclusive?

J

Jeff99

New Member
Hello All-

I thought I knew what I was doing in excel until I found this site. Some of the thing I've seen here are amazing, so first off, a big thanks to all those that contribute.

What I'm trying to do is create an interactive dashboard, but I'm having difficulty figuring out if it's even possible using Check Boxes that are not mutually exclusive. I'm sure it's possible using VBA, but I have only very limited VBA skills, plus my employer likes to lock down macros.

With the check box layout below: Grp 1, Grp 2, Plan A, Plan B, Plan C, I count 21 various combinations (i.e. Grp 1 Plan A, Grp 1&2 Plan B&C, etc.). I've tested using IF statements, so TTTTT, would be to include all, TTTTF, would be Grp 1&2 and Plans A&B, etc., but I have more dimensions than I included in this example and the number of combinations is too great using my method.

Here is the basic premise, use the check boxes to get totals in the Measure 1 and Measure 2 cells at bottom (data would be on seperate tab):

Group Plan Measure 1 Measure 2

Grp 1 Plan A 100 $10,000
Grp 1 Plan B 200 $20,000
Grp 1 Plan C 300 $30,000
Grp 2 Plan A 400 $40,000
Grp 2 Plan B 500 $50,000
Grp 2 Plan C 600 $60,000

Grp 1 Check Box (Can be just Grp 1, just Grp 2 or both)
Grp 2 Check Box

Plan A Check Box (Can be any combination, but must be at least one)
Plan B Check Box
Plan C Check Box

Measure 1 Results
Measure 2 Results

Any suggestions for me?

Thanks!
 
It sounds like you would also get your results by filtering Group col by either a choice or all, and Plan col by choice or all, and then summing the Measure columns. Would that be the easier way to go? AutoFilter in XL 2007+ even utilized checkboxes in its dropdown so people can 'tick' what they want.
 
Thank you Narayan! This works perfectly. I can't say I understand exactly what's going on, but did understand it enough to modify with additional data and measures and get it to work. I've never used the MMULT function, so will have to learn more about this. Thanks again for your response.
 
Luke - Your idea is how I would do it, but this will be for non-excel users and they just want summary amounts. I also tried using the Advanced Filter, with the criteria based upon user input. This doesn't really work either because the advanced filter only lasts for one iteration or the process has to be repeated for any change filter selections.
 
Hello Jeff,

If you have only two conditions to check, ie Group & Plans then you can use SUMIFS

=SUM(SUMIFS(Measure1,Groups,IF(R1:R5,Q1:Q5),Plans,TRANSPOSE(IF(R1:R5,Q1:Q5))))

If you have more than two conditions, then with MATCH

=SUM(IF(ISNUMBER(MATCH(Groups,IF(R1:R5,Q1:Q5),0)+MATCH(Plans,IF(R1:R5,Q1:Q5),0)),Measure1))

Both are Array Formulas
 
You must log in or register to reply here.
Back
Top