Hi all,
Say I'm running a gym and I want to create a dashboard that will show me a nice bar graph of the # of new members we get per month. The catch is I want to be able to dynamically filter it based on multiple criteria at once. For example, # new members per month that are female AND signed up for our gym in Boston AND bought a gold membership. I have a master file each month with thousands of members' info:
Col A: Member Name
Col B: Membership Start Date
Col C: Gender
Col D: Gym Location
Col E: Membership Type
Col F: Monthly Revenue
... and so on, with maybe 100 different other columns with info abut different member properties
Historically I've just set up a bunch of different tables in separate worksheets that use COUNTIFs to give me the total # of new members that meet the date criteria AND whatever other criteria I am interested in. This is easy if I only care about one or two other filtering criteria (ex. membership date AND gender, AND gym location), but the more things I want to cut by (ex. membership date AND gender AND gym location AND membership type), the longer and uglier my COUNTIFS formula becomes and it really slows down excel.
Would this be more effectively set up using arrays in VBA? Or would that not necessarily help in this situation? Ultimately I want to be able to throw in some dropdown menus or checkboxes that will let the user select what criteria they want to filter on.
Say I'm running a gym and I want to create a dashboard that will show me a nice bar graph of the # of new members we get per month. The catch is I want to be able to dynamically filter it based on multiple criteria at once. For example, # new members per month that are female AND signed up for our gym in Boston AND bought a gold membership. I have a master file each month with thousands of members' info:
Col A: Member Name
Col B: Membership Start Date
Col C: Gender
Col D: Gym Location
Col E: Membership Type
Col F: Monthly Revenue
... and so on, with maybe 100 different other columns with info abut different member properties
Historically I've just set up a bunch of different tables in separate worksheets that use COUNTIFs to give me the total # of new members that meet the date criteria AND whatever other criteria I am interested in. This is easy if I only care about one or two other filtering criteria (ex. membership date AND gender, AND gym location), but the more things I want to cut by (ex. membership date AND gender AND gym location AND membership type), the longer and uglier my COUNTIFS formula becomes and it really slows down excel.
Would this be more effectively set up using arrays in VBA? Or would that not necessarily help in this situation? Ultimately I want to be able to throw in some dropdown menus or checkboxes that will let the user select what criteria they want to filter on.
