IF formula based on drop down list
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. When selecting a category from the drop-down list, you want to sum up all corresponding values in Amount column. As the below screenshot shown, if you select AA from the drop down, you will get the result 10+30+80=120. How to achieve it? Methods in this article can do you a favor. Sum values based on selection of drop-down list with formula 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.1. Select a blank cell to output the result, 2. Enter the below formula into it, and then press the Enter key. =SUMIF(A2:A10,D2,B2:B10) From now on, the sum will be adjusted automatically based on the selection of the drop-down list. Notes:
Easily sum values based on selection of drop down list with an amazing toolHere 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. Please do as follows. Before applying Kutools for Excel, please download and install it firstly. 1. Click Kutools > Super LOOKUP > LOOKUP and Sum. See screeshot: 2. Then a LOOKUP and Sum dialog box pops up, you need to finish the below settings:
Then a formula is created automatically in the Output cell. From now on, the sum will be adjusted automatically based on the drop-down item. 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. Related articles:Autocomplete when typing in Excel drop down list Create drop down list from another workbook in Excel Create a searchable drop down list in Excel Auto populate other cells when selecting values in Excel drop down list More tutorial for drop down list... The Best Office Productivity ToolsKutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
|