Add "All" drop down box item

[evongugg]

Hi!
I'm new to this forum.
I have an Excel .xls spreadsheet. I have created a drop down box for items 1 through 12 using data validation. Now I need to add a drop down box item called "All".
=A:A is the correct range for "All" in the vlookup formula behind all this. The range for 1 through 11 is P1:P12. If I type =A:A, Excel replaces the value with 1. I want it to show "All".
The values in the drop down box are used by a vlookup formula.
Hope I have been clear enough.
If there is a better way of creating "All", please let me know.

 

[WinteE]

Just type "All" instead of =A:A. You can include an IF(??="All",A:A,xxx) in the VLOOKUP formula.

 

[evongugg]

Thanks, it helped a lot.
Unfortunately vlookup picks up only the first instance and not all instances, so we will have to rework this spreadsheet.

 

[evongugg]

Can't edit yet, I guess...
We need to change the formula to SUMIF.
Thanks.