How do I sum a drop down list in Google Sheets?

The main purpose of drop-down lists in Google Sheets is to offer options that a user can choose from. It gives users a clear look at all the available options and also makes sure the user selects only the items allowed.

A drop-down list also ensures that there are fewer errors as the user can now choose from a pre-defined list instead of manually typing the cell content.

Google sheets let us use this functionality with ease. With just a few clicks, you can create either a single-cell dropdown or populate a whole row or column with dropdown lists.

However, you will notice that the default Google Sheets dropdown list allows the user to only select a single item from the list.

Often times you may need to select more than one option in a drop-down list. For example, when theres a collection of colors for you to choose from, you might like more than one color.

Or might want to get a list of coding languages the user is proficient in.

In such cases, its possible that the user knows more than one and there is a need to select multiple options from the drop-down.

Therefore, multiple selections in dropdown lists can be quite useful. Unfortunately, this option is not traditionally allowed in Google Sheets. You are only allowed one option at a time.

The good news is that theres a way around this. It is possible to make your dropdown list allow multiple selections by using Google AppScript.

In this article, I will show you how to create a drop-down list that allows multiple selections [something as shown below].

But first, lets start from scratch.

Lets begin by creating a fresh dropdown list from a list of color options.

Click here to get a copy of the Google Sheets that has multiple selections enabled [make a copy to use it].

Allowing Multiple Selections in a Dropdown list [with repetition]

For this tutorial, I will use the following dataset of items and will create the drop-down in cell C1

To create a drop-down list that allows multiple selections, you need to do two things:

1. Create a drop-down list using a list of items
2. Add the function in the Script Editor that will enable multiple selections in the drop-down.

Lets look at each of these steps in detail

Creating the drop-down list

Suppose I have a dataset of items as shown below and I want to create a drop-down list in cell C1.

Below are the steps to so this:

1. Select cell C1 [the one where you want the drop-down]
2. Click the Data option in the menu
3. Click on Data Validation
4. In the Data Validation dialog box, make sure the Cell range refers to the cell where you want the drop-down
5. In Criteria, select List from a range and then select the range that has the items that you want to show in the drop-down.
6. Click on Save

Your dropdown will now appear in your designated cell [C1 in this example]. When you click on the arrow you will see your list of options.

Notice now that you are only allowed to select one option at a time.

Now let me show you how to convert this drop-down [which allows only one item to be displayed in the cell] to the one that allows multiple selections.

And to do that, you need to add the function script in the Google Sheets script editor.

Adding the Google Apps Script to Enable Multiple Selections

Below is the script code that you would have to copy and paste in the script editor [steps mentioned below section after the code]:

function onEdit[e] { var oldValue; var newValue; var ss=SpreadsheetApp.getActiveSpreadsheet[]; var activeCell = ss.getActiveCell[]; if[activeCell.getColumn[] == 3 && activeCell.getRow[] == 1 && ss.getActiveSheet[].getName[]=="Sheet1"] { newValue=e.value; oldValue=e.oldValue; if[!e.value] { activeCell.setValue[""]; } else { if [!e.oldValue] { activeCell.setValue[newValue]; } else { activeCell.setValue[oldValue+', '+newValue]; } } } }

Below are the steps to add this script code in the Google Sheets backend so that the drop-down we created in cell C1 can allow selecting more than one option:

1. Click the Tools option in the menu
2. Click on Script Editor. This will open the Script Editor in a new window
3. In the Code.gs window, remove anything that is already there and copy and paste the above macro code
4. Click on the Save button in the toolbar [or use the keyboard shortcut Control + S]
5. Give the project a name [you only need to do this once]
6. Close the Script Window [if you want to]

Now come back to the worksheet and try making multiple selections in the drop-down. For example, first, select Apple and then select Banana.

You will notice that it takes a second [sometimes two seconds] and will then show you both the selected items [separated by a comma].

Also note that with this code, it will allow you to select the same item twice. For example, if you select Apple and then select Apple again, it will show it twice in the cell.

If you want to create a drop-down list that allows multiple selections without repetition, I have provided the code later in this tutorial.

How does the code work?

Lets try to understand this code part by part.

The code starts with the line

function onEdit[e]

onEdit[] is a special function on Google Sheets. It is also known as an event handler. This function is triggered every time there is a change in your spreadsheet.

We want our multiple selection code to run every time an item is selected from the dropdown list, so it makes sense to put our code in the onEdit[] function.

Now, the AppScript passes this function as an event object as an argument. Typically, the event object is called e. This event object contains information about the event triggered.

If you know the basics of AppScript, you will find the first four lines quite easy to understand:

var oldValue; var newValue; var ss=SpreadsheetApp.getActiveSpreadsheet[]; var activeCell = ss.getActiveCell[];

I have declared two variables one [oldValue] that will hold the old value of the cell and another [newValue] that will hold the new value of the cell.

The variable activeCell will hold the currently active cell that has been edited.

Now, we dont want the code to run every time any cell is edited. We only want it to run when cell CA1 of Sheet1 is edited. So we make sure of that by using an if statement:

if[activeCell.getColumn[] == 3 && activeCell.getRow[] == 1 && ss.getActiveSheet[].getName[]=="Sheet1"]

The above code checks row and column number of the active cell and the sheet name. Since out drop-down is in cell C1, it checks whether the row number is 1 or not and whether the column number is 3 or not.

Only when all these three conditions are met that the code within the IF statement is executed.

Below is the code that is executed when we are on the right cell [C1 in our example]

newValue=e.value; oldValue=e.oldValue;

e.oldValue is also a property of the event object, e. This holds the previous value of the active cell. In our case, this would be the value before we make the drop-down selection

We want to assign this to the variable oldValue.

e.value is a property of the event object, e. This holds the current value of the active cell. We want to assign this to the variable newValue.

First, let us consider what happens if no option is selected. In that case, e.value will be undefined. When this happens, we do not want anything displayed in cell A1. So we put a blank value on the cell.

This will also be the case if the user decides to delete all previous selections and restart from scratch.

if[!e.value] { activeCell.setValue[""]; }

If the user does select an option, then the lines following the else statement will be executed. We now want to specify what to do if an option is selected for the first time from the drop-down list.

That means e.oldValue is undefined. When this happens, we want only the selected option[newValue] to be displayed in cell A1.

if [!e.oldValue] { activeCell.setValue[newValue];

Finally, we specify what to do the next time onwards that an option is selected. That means when both e.value and e.oldValue hold specific values.

else { activeCell.setValue[oldValue+', '+newValue]; }

Once you are one typing the code, save it and then try making multiple selections from your dropdown list. You will find all your selected options displayed one by one, separated by commas.

If you make a mistake, you can always clear the cell and start over. When this happens, we want to display both the previous values and the newly selected value in cell A1, all separated by commas.

Note: When you use the above code, it will not allow you to go back and edit part of the string. For example, if you want to manually edit the item string or want to delete a part of it, you wont be able to do this. You will have to delete all the cell content and start over if you want to make any changes.

There is, however, a small problem with this. Notice that if you select an item more than once, it will again be entered in your list of selections. In other words, repetition is allowed. But usually, we do not want that.

Below, I have provided details of how you can make changes to your code to make sure an item can only be selected once so that there are no repetitions.

Allowing Multiple Selections in a Dropdown list [without repetition]

Below is the code that will allow multiple selections in the drop-down without repetitions.

function onEdit[e] { var oldValue; var newValue; var ss=SpreadsheetApp.getActiveSpreadsheet[]; var activeCell = ss.getActiveCell[]; if[activeCell.getColumn[] == 3 && activeCell.getRow[] == 1 && ss.getActiveSheet[].getName[]=='Sheet1'] { newValue=e.value; oldValue=e.oldValue; if[!e.value] { activeCell.setValue[""]; } else { if [!e.oldValue] { activeCell.setValue[newValue]; } else { if[oldValue.indexOf[newValue]