Sum Adjacent Column Value If Cell Value Contains Substring

[crypticツ]

App: Calc

Version: 4.2.1.1

Build ID: 420m0(Build:1)

I have the following where I have various items with a weight per item, count of how many of that item, the total weight of item and the location ID(s) where that item will be.

   A            B           C           D                   E
1. Item Name    Weight      Count       Total Weight        Location ID(s)
2. ItemName1    53          4           =PRODUCT(B3*C3)     4,6
3. ItemName2    23          6           =PRODUCT(B4*C4)     1,5
4. ItemName3    85          15          =PRODUCT(B5*C5)     1,3,4
5. ItemName4    12          8           =PRODUCT(B6*C6)     2

I then have the following, which corresponds to the location IDs in the above.

   A                B      C
1. Location Name    ID     Total Weight
2. Name1            1      494 = (D3 / 2 (comma items in E3)) + (D4 / 3 (comma items in E4))
3. Name2            2      96  = (D5 / 1 (comma items in E5))
4. Name3            3      425 = (D4 / 3 (comma items in E4))
5. Name4            4      531 = (D2 / 2 (comma items in E2)) + (D4 / 3 (comma items in E4))
6. Name5            5      69  = (D3 / 2 (comma items in E3))
7. Name6            6      106 = (D2 / 2 (comma items in E2))

What I am trying to do is calculate the total weight the a location will have. This is done by finding all E cells which contain the substring of the column ID. If the cell contains that ID I would then get the D value for that row and divide it’s value by the total amount of location IDs for that item. That value calculated would be summed up for all items which contain that location ID.

I have no idea where to begin on this. Is it even possible to match by substring and also get a count of how many comma separated IDs there are? I’m kinda new to Calc so if there is a better way for me to do this please tell me.

[mariosv]

A formula like: =SUMIF($E$2:$E$5;".*"&$B2&".*";$D$2) must work where $B2 is the ID in the second table. It needs the regular expressions active, Menu/Tools/Options/LibreOffice calc/calculation.

Sample file attached:
Sum_ID_locations.ods

edited: 2014-02-24

For more than one condition you can use: =SUMIFS($D$2:$D$6;$A$2:$A$6;$A9;$E$2:$E$6;".*"&B9&".*")

SUMIF(SumRange; Range; Criteria; Range; Criteria;... Range; Criteria;)

attached new sample.
Sum_Item_ID_locations

In any case the best will be have one line for location, make easy filter the data and then you can use the pivot table.

[crypticツ]

I figured out how to count the number of location IDs =LEN(ref_cell)-LEN(SUBSTITUTE(ref_cell,",",""))+1 Could you please update your answer so it uses that to do the proper calculation alongside your formula? Once working will mark as answered and remove all these cluttering comments. Thank you for your help.

[crypticツ]

Both files incorrectly calculate the values. I ended up creating a new hidden column which is the total weight of the item divided by the value of above formula. I then changed your first formula to grab that value instead of the total weight value. I’m sorry if I’m not being clear about what I’m needing. I don’t know how to combine the two formulas so that I would not need the hidden column though. I’m going to mark as accepted, but would appreciate an answer on how to combine the formulas.