How to add Multiple Cells from Data Validation List

**trumpetman** · 11-21-2014, 09:38 PM

I understand VLOOKUP but I am not sure how to add the range up.

I have attached a worksheet. I want to be able to Select from the list the name of the employee, then the month, then the product and then return the total discounts added together from the table. For example if I selected Mark for Feb and Product energy it return the total discount which would be £300.

What I would also like to is be able to not select a box and then just add the selected cells as well. For example I do not select a name but select March for the month and General for the product and that would return 498. I could decided I chose a name not the month (so it would then add up the yr) and then the product.

I am wondering if VLOOKUP is the way I should be doing this with a SUM formula or SUMIF as someone has suggested, not show if that would work.

Any Ideas?

**teylyn** · 11-21-2014, 10:33 PM

Hello,

may I suggest a pivot table as an alternative to a formula approach? Drag Name, Month and Product into the row area, Discount into the Values area. Use a tabular layout and don't use subtotals. Now you can filter on each column with the drop-down arrows at the top of the pivot table.

2014-11-22_15-31-26.png

See attached file.

cheers, teylyn

**ELeGault** · 11-22-2014, 01:08 AM

Pivot table is always great for quick analysis... But if your heart is content on formulas within the sheet there are a few approaches you can take!

For your sheet, I would probably throw this at it.

Add a preceding column to your table to concatenate the three criteria's into one cell.
In your sheet this would look like this...
Insert into A3 and drag down... =CONCATENATE(IF($J$4=0,"",$B3),IF($K$4=0,"",$C3),IF($L$4=0,"",$E3))
You will notice the multiple If statements in this concatenate statement. This is to build a unique lookup value based on what you have chosen in your list.
I do recommend naming your values so that it becomes more intuitive to read, like so...=CONCATENATE(IF(Selected_Name=0,"",$B3),IF(Selected_Month=0,"",$C3),IF(Selected_Product=0,"",$E3))
Another side note to that naming, should you decide to name anything in your workbook (Highly Recommended) then use descriptive names to avoid name/formula conflicts. If you were to name it Month, you would run into a similar name which can lead to confusion.

Now your formula to add up the discount would infact be a sumif statement, however, I always recommend doing away with sumif and countif and use the multiple criteria versions.
In this case sumifs.

Total Discount =SUMIFS(H2:H10,A2:A10,CONCATENATE(J4,K4,L4))
This will give you a sum of the total discounts applied that meet the selected criteria in your sheet.

**trumpetman** · 11-22-2014, 11:05 AM

Thanks guys.

So a simple formula would not do what I was looking to like VLOOKUP or =SUMIF?

**Tony Valko** · 11-22-2014, 11:14 AM

Your first request is fairly easy.

The second request is a bit more complicated but doable. However, I'm not sure what this means:

I could decided I chose a name not the month (so it would then add up the yr) and then the product.

"Simple" is a relative term!

**trumpetman** · 11-23-2014, 07:12 PM

Sorry, it meant I could choose a name but leave the month cell blank and that way it would not return a specific month but the year's data.

**teylyn** · 11-23-2014, 07:45 PM

No, a simple formula won't do this rather complex job. You want a formula that takes any or all of three possible parameters as conditions to sum up a lot of data. That cannot be done with a simple Vlookup or a simple Sumif. It can be done with a simple pivot table, without involving any formula. If you want formula, then it won't be simple.

**teylyn** · 11-23-2014, 07:51 PM

Another simple approach that does not involve formulas is:

Select any cell in the data table and hit Ctrl-T. This will convert the range into an Excel Table. You now see the Table ribbon, the range has a banded row formatting and there are filter drop-downs on each column header. On the table ribbon, click "Total Row". Now the discount column is summed up. Use the filter drop-downs to filter by name, month or product, or any combination of these. The total row will show the result.

**Tony Valko** · 11-23-2014, 08:09 PM

See if this array formula** does what you want.

=SUM(IF(J4="",ROW(B3:B10)^0,--(B3:B10=J4))*IF(K4="",ROW(C3:C10)^0,--(C3:C10=K4))*IF(L4="",ROW(E3:E10)^0,--(E3:E10=L4))*H3:H10)

If no criteria are selected the formula will sum ALL of the discount in column H. If you don't want that to happen then use this version (still array entered**):

=IF(COUNTA(J4:L4),SUM(IF(J4="",ROW(B3:B10)^0,--(B3:B10=J4))*IF(K4="",ROW(C3:C10)^0,--(C3:C10=K4))*IF(L4="",ROW(E3:E10)^0,--(E3:E10=L4))*H3:H10),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**kvsrinivasamurthy** · 11-24-2014, 04:16 AM

PL see attached file.

**teylyn** · 11-24-2014, 04:42 AM

kvsrinivasamurthy, why hide your solution in an attachment? Please post your formula and your explanations in a comment, and explain how it works. That way, everybody can learn from your approach.

**kvsrinivasamurthy** · 11-24-2014, 05:31 AM

@ teylyn
I am not hiding formula. I thought it would be more easy to understand formula in a workbook rather than posting only formula.

**teylyn** · 11-24-2014, 05:33 AM

Yeah, but post the formula in the thread, so we can see what you suggest without opening the file, please. If we then want to see how it works in a real spreadsheet, we can open the file.

**Tony Valko** · 11-24-2014, 11:29 PM

Slight tweak.

We can save a few keystrokes...

=SUM(IF(J4="",ROW(B3:B10)^0,B3:B10=J4)*IF(K4="",ROW(C3:C10)^0,C3:C10=K4)*IF(L4="",ROW(E3:E10)^0,E3:E10=L4)*H3:H10)

=IF(COUNTA(J4:L4),SUM(IF(J4="",ROW(B3:B10)^0,B3:B10=J4)*IF(K4="",ROW(C3:C10)^0,C3:C10=K4)*IF(L4="",ROW(E3:E10)^0,E3:E10=L4)*H3:H10),"")

Still array entered.

How to add Multiple Cells from Data Validation List

LinkBack

Thread Tools

Rate This Thread

Display

How to add Multiple Cells from Data Validation List

Re: How to add Multiple Cells from Data Validation List

Re: How to add Multiple Cells from Data Validation List

Re: How to add Multiple Cells from Data Validation List

Re: How to add Multiple Cells from Data Validation List

Re: How to add Multiple Cells from Data Validation List

Re: How to add Multiple Cells from Data Validation List

Re: How to add Multiple Cells from Data Validation List

Re: How to add Multiple Cells from Data Validation List

Re: How to add Multiple Cells from Data Validation List

Re: How to add Multiple Cells from Data Validation List

Re: How to add Multiple Cells from Data Validation List

Re: How to add Multiple Cells from Data Validation List

Re: How to add Multiple Cells from Data Validation List

Thread Information

Users Browsing this Thread

Similar Threads

Data Validation - How to update cells when a name is selected from a Data Validation List

Auto-Generate List from reference cells based on data validation list selection

Adding Date Data Validation to cells with List Data Validation

Data Validation List link to cells that are not in the format I want in the List.

data validation--multiple dependent list

Bookmarks

Bookmarks

Posting Permissions