Sum Based on Checkboxes

[Vassago]

Hello,

I have a spreadsheet sample attached below. Basically, I have a sheet
with the follow fields:

TOTALS 210 460 45.7%
Toy Color Accts Atts Pass
Ball Blue 20 100 20.0%
Ball Black 30 90 33.3%
Bike Blue 40 70 57.1%
Frisbee Red 60 55 109.1%
Toy Blue 50 60 83.3%
Star Red 10 85 11.8%

What I would like to do is set up an autofilter using checkboxes. I
would like to create a series of checkboxes that will show above the
sheet each labelled like "Red", "Blue", and "Black" where a user can
select either one color, multiple colors, or all colors, and the list
will only show the colors specified. I also want it to recalculate the
totals based on the checkboxes selected. Does anyone have any ideas as
I am stumped for the moment.

Thanks!

Vassago


+-------------------------------------------------------------------+
|Filename: Sample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5125 |
+-------------------------------------------------------------------+

 

[Biff]

Hi!

Does anyone have any ideas

Yes. Why reinvent the wheel?

Autofilter can already do this!

You could link each checkbox to a cell and based on the check state use a
formula in a helper column (hidden) and then autofilter on that helper
column.

Biff

 

[Biff]

in a helper column (hidden)

Well, forget about the hidden part. It'd be kind of hard to select the
filter from hidden column!

Biff

 

[Vassago]

Thanks for the replies!

Now I'm even more lost than I started.

Can autofilter be used to filter more than one color? I would need
this capability. I also though autofilter would not filter totals,
just the actual lists. Can you elaborate on this for me please in baby
talk? As much as I know in Access I lack in Excel.

Thanks!

Vassago

 

[Biff]

Ok......

Here's a small sample file:

Checkbox filter.xls 17kb

http://cjoint.com/?hDuiGzsJTz

I used checkboxes from the Forms toolbar.

The checkboxes are linked to the cells directly above them. For example: the
checkbox in B2 is linked to B1, the checkbox in C2 is linked to C1. When you
"check" a checkbox its check state is TRUE. When you "uncheck" a checkbox
its check state is FALSE. The check state is returned to the linked cell.
You can't see the check state because I set the font color of the linked
cells to be the same as the fill color.

Column F is the helper column and contains a formula to determine which
checkboxes have been checked and compares that to the color entry in column
B.

The totals are calculated using the SUBTOTAL function.

An Autofilter has been applied to row 7.

So, check the color(s) you want then filter on "x" in column F.

Biff

 

[Vassago]

Awesome! The Subtotal function was the missing link for me. I didn'
know that existed.

Thank you so much for your help. I've learned a lot

 

[Biff]

You're welcome. Thanks for the feedback!

Biff