Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 24 Feb 2014 06:38:55 +0100Sum Adjacent Column Value If Cell Value Contains Substringhttps://ask.libreoffice.org/en/question/30283/sum-adjacent-column-value-if-cell-value-contains-substring/**App:** Calc<br>
**Version:** 4.2.1.1<br>
**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.Sun, 23 Feb 2014 22:30:38 +0100https://ask.libreoffice.org/en/question/30283/sum-adjacent-column-value-if-cell-value-contains-substring/Answer by m.a.riosv for <p><strong>App:</strong> Calc<br>
<strong>Version:</strong> 4.2.1.1<br>
<strong>Build ID:</strong> 420m0(Build:1)</p>
<p>I have the following where I have various items with a <em>weight</em> per item, <em>count</em> of how many of that item, the <em>total weight</em> of item and the <em>location ID(s)</em> where that item will be.</p>
<pre><code> 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
</code></pre>
<p>I then have the following, which corresponds to the location IDs in the above.</p>
<pre><code> 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))
</code></pre>
<p>What I am trying to do is calculate the <em>total weight</em> the a location will have. This is done by finding all <code>E</code> cells which contain the substring of the column <em>ID</em>. If the cell contains that ID I would then get the <code>D</code> value for that row and _<strong>divide</strong> 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.</p>
<p>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.</p>
https://ask.libreoffice.org/en/question/30283/sum-adjacent-column-value-if-cell-value-contains-substring/?answer=30288#post-id-30288A 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](/upfiles/13931962227577356.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](/upfiles/13932085156264005.ods)
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.Sun, 23 Feb 2014 23:57:39 +0100https://ask.libreoffice.org/en/question/30283/sum-adjacent-column-value-if-cell-value-contains-substring/?answer=30288#post-id-30288Comment by cryptic ツ for <p>A formula like: <code>=SUMIF($E$2:$E$5;".*"&$B2&".*";$D$2)</code> must work where $B2 is the ID in the second table. It needs the regular expressions active, Menu/Tools/Options/LibreOffice calc/calculation.</p>
<p>Sample file attached:
<a href="/upfiles/13931962227577356.ods">Sum_ID_locations.ods</a></p>
<p>edited: 2014-02-24</p>
<p>For more than one condition you can use: <code>=SUMIFS($D$2:$D$6;$A$2:$A$6;$A9;$E$2:$E$6;".*"&B9&".*")</code></p>
<pre><code>SUMIF(SumRange; Range; Criteria; Range; Criteria;... Range; Criteria;)
</code></pre>
<p>attached new sample.
<a href="/upfiles/13932085156264005.ods">Sum_Item_ID_locations</a></p>
<p>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.</p>
https://ask.libreoffice.org/en/question/30283/sum-adjacent-column-value-if-cell-value-contains-substring/?comment=30300#post-id-30300I 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.Mon, 24 Feb 2014 04:05:12 +0100https://ask.libreoffice.org/en/question/30283/sum-adjacent-column-value-if-cell-value-contains-substring/?comment=30300#post-id-30300Comment by cryptic ツ for <p>A formula like: <code>=SUMIF($E$2:$E$5;".*"&$B2&".*";$D$2)</code> must work where $B2 is the ID in the second table. It needs the regular expressions active, Menu/Tools/Options/LibreOffice calc/calculation.</p>
<p>Sample file attached:
<a href="/upfiles/13931962227577356.ods">Sum_ID_locations.ods</a></p>
<p>edited: 2014-02-24</p>
<p>For more than one condition you can use: <code>=SUMIFS($D$2:$D$6;$A$2:$A$6;$A9;$E$2:$E$6;".*"&B9&".*")</code></p>
<pre><code>SUMIF(SumRange; Range; Criteria; Range; Criteria;... Range; Criteria;)
</code></pre>
<p>attached new sample.
<a href="/upfiles/13932085156264005.ods">Sum_Item_ID_locations</a></p>
<p>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.</p>
https://ask.libreoffice.org/en/question/30283/sum-adjacent-column-value-if-cell-value-contains-substring/?comment=30302#post-id-30302Both 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.Mon, 24 Feb 2014 06:38:55 +0100https://ask.libreoffice.org/en/question/30283/sum-adjacent-column-value-if-cell-value-contains-substring/?comment=30302#post-id-30302