0

I have an excel sheet with multiple dropdown selection, where items are binded with master data in sheet 2. Master data contains items and their respective values. Now I am using a formula for sum of items selected in the dropdown list as below

=SUMPRODUCT(ISNUMBER(SEARCH(PRODUCT,J4))*AMOUNT)

where PRODUCT & AMOUNT is master data and J4 is column reference of multiple selection dropdown list. Code works well but whenever I select duplicate item in dropdown list the amount of duplicate item is not reflected in the SUM column where the code is addedd. However the count of items selected works well with below formula

=IF(J4="","",(LEN(J4)-LEN(SUBSTITUTE(J4,CHAR(10),""))+1))

Data:

data

| PRODUCT | AMOUNT |
|---------|--------|
| Apple   | 100    |
| Grapes  | 200    |
| Orange  | 50     |
| Mango   | 500    |
| Guava   | 350    |
| Cherry  | 170    |

Expected result:

It's multiple selection dropdown. In second row I have selected Apple, Grapes & Orange so it counts 350 (100 + 200 + 50). Now in 1st row if I select Apple again then the cost of apple should be added in sum i.e. it should be 450 (100 + 200 + 50 + 100)

QHarr
  • 83,427
  • 12
  • 54
  • 101
user3648104
  • 11
  • 3
  • Why down vote ? – user3648104 Jul 16 '18 at 06:44
  • @QHarr Image added for more description – user3648104 Jul 16 '18 at 06:59
  • Where is J4 in all of this? Is it the products column on the left? – QHarr Jul 16 '18 at 07:11
  • And how does Apples end up at 350, even if only once, as value is 100 on right hand side? A bit more explanation would help. – QHarr Jul 16 '18 at 07:12
  • Its multiple selection dropdown. In second row I have selected Apple, Grapes & Orange so it counts 350 (100 + 200 + 50). Now in 1st row if I select Apple two times then the sum should be Apple, Grapes, Orange & Apple 450 (100 + 200 + 50 + 100). I think it clears the doubt :) – user3648104 Jul 16 '18 at 07:16

1 Answers1

0

Generic:

You want a generic formula of:

=SUMPRODUCT(SUMIF(PRODUCT,array,AMOUNT))

Specific example:

For top box e.g.

=SUMPRODUCT(SUMIF(PRODUCT,FILTERXML("<t><s>" & SUBSTITUTE(B2,CHAR(10), "</s><s>") & "</s></t>", "//s"),AMOUNT))

Based on your file:

Data

Note I use FILTERXML to create the array of values from the drop down selection. Char(10) is for your new line.

See my explanation of FILTERML here.

Example run:

Example

QHarr
  • 83,427
  • 12
  • 54
  • 101
  • The column reference is J4 where multiple dropdown are located. So E3:E6 replaced with J4 but it shows sum as zero (0). J4 is single cell where we can select multiple items in dropdown and displays in new line But the cell remains single. – user3648104 Jul 16 '18 at 07:30
  • J4 is single cell with items with new line (vbNewLine) – user3648104 Jul 16 '18 at 07:33
  • Please see I have uploaded the excel some column hided pass if asked is 'test' https://www.dropbox.com/s/pp5evikosfctp9o/Book2%20-%20Copy.xlsm?dl=0 – user3648104 Jul 16 '18 at 07:49
  • No its not working, when I select item form dropdown the value is showing 0. I dont know I have missed something – user3648104 Jul 16 '18 at 08:15
  • Yes it was correct. But when I select another item in dropdown it shows as 0 – user3648104 Jul 16 '18 at 08:19
  • Hmmmm will try. Request you to kindly look into in your spare time. – user3648104 Jul 16 '18 at 08:28
  • Its multiple selection dropdown. The logic code written in VBA code of the sheet. – user3648104 Jul 17 '18 at 15:31
  • You can select one item then again select one more item and so on.... But the summation code is not working yet – user3648104 Jul 19 '18 at 11:20
  • It seems to work now for me. Same file http://s000.tinyupload.com/index.php?file_id=00724137915813781229 – QHarr Jul 19 '18 at 12:25
  • 1
    I downloaded your file but is dosen't count the duplicate items :( By the way I am very great full for your kind help. :) – user3648104 Jul 20 '18 at 11:08
  • Try the following. Delete the contents of cell B2. Then add Apple twice. I get 200 in D2 which is summing including the duplicates. – QHarr Jul 20 '18 at 11:27