How do I Make a Drop Down with a Catch All?

[Shinano]

Hello all you wise men & women,

After more than a decade away from this forum - you know, Life? - I managed to make my way back in after a long time of jogging my memory about my credentials. Man, Excel has evolved over the years...

Anyway, I have question for all you great people. I am trying to make a drop down menu with a "catch all values", or whatever you want to call it. Like:

Drop down items:

1. A
2. B
3. C
4. Sum of A, B & C

For 1., 2. & 3. I am fine, I used List. But, I can not get my head around 4. I managed to come up with a solution adding: '<>""' as the fourth item in the List. That appears to work and give me the outcome that I want, but in the drop down list it looks like, well: <>"" Do we agree that does not look very slick?

I am looking for something saying:"All Values", or "Total", or something like that while at the same time catching the sum of the other values combined.

How would you go about that?

Thank you very much in advance.

 

[etaf]

what version of excel
you could use an IF

in the list - add something like ALL

then use an IF
=IF( dropdown cell = "ALL" , SUM(Range) , SUMIF(Range , Range , dropdowncell)

for example

Filter values using a list as criteria.xlsx
	A	B	C	D										N	O
1	a	1													data validation list
2	b	2		all											a
3	c	3													b
4	a	4													c
5	c	5													d
6															all
7
8				15
Sheet1

Cell Formulas
Range	Formula
D8	D8	=IF(D2="All",SUM(B1:B5),SUMIF(A1:A5,D2,B1:B5))

Cells with Data Validation
Cell	Allow	Criteria
D2	List	=$O$2:$O$6

 

[Micron]

You're using vba to do something with the choices or is this about a formula? If vba then add ALL to the value list and sum/concatenate all of the list choices. To concatenate it could be like this:

Sub concatListItems()
Dim i As Integer
Dim str As String

For i = 0 To UserForm.ListBox2.ListCount - 1
     str = str & UserForm.ListBox2.List(i) & ", "
Next
str = Left(str, Len(str) - 2)
Debug.Print str

End Sub