Multiple Selections from a dropdown list (w/o) VBA

[zinah]

Hello!

I have below table with dummy data and what I need is to:

- Choose multiple selections from the drop down list without a VBA, is there any trick for that? if not what is the suitable VBA code/macro that you suggest
- When I choose multiple selections, what should I update in my current Summary formula in order to consider the additional selection(s)?

Thank you!

Book3
	A	B	C	D	E	F	G	H	I							P	Q	R	S	T
1
2
3		Team Name	Team 1
4
5		Country	Country 1														Data
6						Summary											Team Name	Country	Include Status
7		Include Status	Yes			5											Team 1	Country 3	Yes
8																	Team 2	Country 2	Yes
9																	Team 3	Country 1	Yes
10																	Team 2	Country 2	No
11																	Team 3	Country 2	Yes
12																	Team 3	Country 1	Yes
13																	Team 1	Country 2	No
14																	Team 2	Country 2	No
15																	Team 3	Country 3	Yes
16																	Team 1	Country 3	Yes
17																	Team 2	Country 2	Yes
18																	Team 3	Country 1	Yes
19																	Team 1	Country 3	Yes
20																	Team 2	Country 2	Yes
21																	Team 3	Country 1	Yes
22																	Team 1	Country 3	Yes
23																	Team 2	Country 2	Yes
24																	Team 3	Country 1	Yes
25																	Team 1	Country 1	Yes
26																	Team 1	Country 1	Yes
27																	Team 1	Country 1	Yes
28																	Team 1	Country 1	Yes
29																	Team 1	Country 1	No
30																	Team 1	Country 1	Yes
31																	Team 3	Country 1	Yes
32																	Team 3	Country 1	Yes
33																	Team 3	Country 1	Yes
34																	Team 3	Country 1	No
35																	Team 3	Country 1	Yes
36																	Team 3	Country 1	Yes
37																	Team 2	Country 1	Yes
38																	Team 2	Country 1	Yes
39																	Team 2	Country 1	No
40																	Team 2	Country 1	Yes
41																	Team 2	Country 1	No
42																	Team 2	Country 1	Yes
43
44
Sheet1

Cell Formulas
Range	Formula
F7	F7	=COUNTIFS($Q$7:$Q$42,$C$3,$R$7:$R$42,$C$5,$S$7:$S$42,C7)

Cells with Data Validation
Cell	Allow	Criteria
C3	List	Team 1, Team 2, Team 3
C7	List	Yes, No
C5	List	Country 1, Country 2, Country 3

 

[severynm]

If you really want to do it without VBA, your only option is to add the data to a pivot table and then use a slicer for each field in place of the drop downs.

Using a pivot table, you wouldn't need a formula for summary - simply use the Count of any of your fields. The value will automatically change as you adjust the slicers.

 

[zinah]

If you really want to do it without VBA, your only option is to add the data to a pivot table and then use a slicer for each field in place of the drop downs.

Using a pivot table, you wouldn't need a formula for summary - simply use the Count of any of your fields. The value will automatically change as you adjust the slicers.

Thank you for your suggestion! The main reason why I'm doing this is not to use the pivots, as currently I'm using this approach. But to your point if there's no option but VBA, then I need help with that.

 

[severynm]

Gotcha. If you didn't want to use pivot tables, you could also try to build the dropdowns with old fashioned data validated lists, and since you're in 365 you can make use of the FILTER function to take care of the rest. The formula would be a little cleaner if you add your data to an excel table so you can use structured references, but as it stands, you can count your results with

=ROWS(FILTER(Q7:S42,(Q7:Q42=C3)*(R7:R42=C5)*(S7:S42=C7),0))

Filter() returns a filtered table according to your logic, and Rows() counts the rows returned in the Filter.