If there is method for allowing to auto complete when typing the first letter in the drop down list, everything will become easier.
If you have a data validation drop down list with large values, you need to scroll down in the list just for finding the proper one, or type the whole word into the list box directly.
If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.Īutocomplete when typing in Excel drop down list From now on, the sum will be adjusted automatically based on the drop-down item. Then a formula is created automatically in the Output cell. 2.3) In the Options section, choose the Return the sum of all matched values option.2.2) In the Select Range section, specify the Lookup Value, Output Range as well as the Data table range.2.1) In the Lookup and Sum Type section, select Lookup and sum matched values(s) in row(s) option.Then a LOOKUP and Sum dialog box pops up, you need to finish the below settings: Click Kutools > Super LOOKUP > LOOKUP and Sum.
Please do as follows.īefore applying Kutools for Excel, please download and install it firstly.ġ. Here recommend a handy tool – the LOOKUP and Sum utility of Kutools for Excel to help you easily sum values based on drop-down list selection without applying formulas. In the formula, A2:A10 is the category range, D2 is the drop-down list cell, and B2:B10 is the value range you need to sum based on the drop-down list selection. You can also apply the below formula to sum values based on selection of drop-down list.
Enter the below formula into it, and then press the Enter key.įrom now on, the sum will be adjusted automatically based on the selection of the drop-down list. Select a blank cell to output the result,Ģ. Sum values based on selection of drop-down list with formulaYou can apply the following formulas to sum values based on selection of drop-down list in Excel.ġ. Sum values based on selection of drop-down list with formulaĮasily sum values based on selection of drop down list with an amazing tool
How to achieve it? Methods in this article can do you a favor. As the below screenshot shown, if you select AA from the drop down, you will get the result 10+30+80=120. When selecting a category from the drop-down list, you want to sum up all corresponding values in Amount column. Supposing you have a table contains two columns (Category and Amount), and you have created a data validation drop-down list which contains all the categories. Now, head back to your working sheet and click the cell or cells that you'd like to validate.How to sum values based on selection of drop-down list in Excel? The list can be any length you'd like and can include any type of information - some common list items are Yes/No (often abbreviated as Y/N), Pass/Fail, numbered lists, and dates. To do this, head to your blank sheet and list the items that you'd like to include in your drop-down in Column A. In order to perform this operation, you will need at least two sheets open in your workbook: a working sheet and a blank sheet where you can compile your lists. This function is very useful in a number of cases, from planning employee vacation schedules, to monitoring expenses, to general project management. When applied, the Data Validation function serves to restrict data that can be entered in the target cell, requiring users to select a value from a list of pre-determined inputs. This article will teach you how to create drop-down lists in Microsoft Excel using a feature called Data Validation.