Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 28 Jan 2018 15:57:53 +0100How to make a sum in B2 of all cells in B where the corresponding A contains A2 and D is 1.https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/Hi
I would be so happy to get this complete formula. Please use the example file.
Klaus
[C:\fakepath\Example sumifs.ods](/upfiles/15169139955168667.ods)Thu, 25 Jan 2018 22:00:19 +0100https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/Answer by Mike Kaganski for <p>Hi </p>
<p>I would be so happy to get this complete formula. Please use the example file.</p>
<p>Klaus</p>
<p><a href="/upfiles/15169139955168667.ods">C:\fakepath\Example sumifs.ods</a></p>
https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?answer=144116#post-id-144116`=SUMPRODUCT(B4:B9;NOT(ISERROR(SEARCH($A$2;A4:A9)));D4:D9=1)`
`=SUMPRODUCT(one.B4:B9;NOT(ISERROR(SEARCH($A$2;one.A4:A9)));one.D4:D9=1)`Thu, 25 Jan 2018 22:17:47 +0100https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?answer=144116#post-id-144116Comment by joe_hayden for <p><code>=SUMPRODUCT(B4:B9;NOT(ISERROR(SEARCH($A$2;A4:A9)));D4:D9=1)</code></p>
<p><code>=SUMPRODUCT(one.B4:B9;NOT(ISERROR(SEARCH($A$2;one.A4:A9)));one.D4:D9=1)</code></p>
https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?comment=144122#post-id-144122Slightly confused about what is being asked, though different way of doing the same thing =SUMIFS(B4:B9,A4:A9,A2,D4:D9,1)Fri, 26 Jan 2018 00:04:06 +0100https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?comment=144122#post-id-144122Comment by inJesus for <p><code>=SUMPRODUCT(B4:B9;NOT(ISERROR(SEARCH($A$2;A4:A9)));D4:D9=1)</code></p>
<p><code>=SUMPRODUCT(one.B4:B9;NOT(ISERROR(SEARCH($A$2;one.A4:A9)));one.D4:D9=1)</code></p>
https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?comment=144131#post-id-144131Thank you to both of you. Both answers seem to work here. Blessings, KlausFri, 26 Jan 2018 06:44:01 +0100https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?comment=144131#post-id-144131Comment by Mike Kaganski for <p><code>=SUMPRODUCT(B4:B9;NOT(ISERROR(SEARCH($A$2;A4:A9)));D4:D9=1)</code></p>
<p><code>=SUMPRODUCT(one.B4:B9;NOT(ISERROR(SEARCH($A$2;one.A4:A9)));one.D4:D9=1)</code></p>
https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?comment=144133#post-id-144133Hmmm... the SUMIFS variant should only catch places where A is *equal* to A2? to contain, the `A2` should change to `".*"&A2&".*"` (given that regular expressions are used in formulas).Fri, 26 Jan 2018 07:14:27 +0100https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?comment=144133#post-id-144133Comment by inJesus for <p><code>=SUMPRODUCT(B4:B9;NOT(ISERROR(SEARCH($A$2;A4:A9)));D4:D9=1)</code></p>
<p><code>=SUMPRODUCT(one.B4:B9;NOT(ISERROR(SEARCH($A$2;one.A4:A9)));one.D4:D9=1)</code></p>
https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?comment=144287#post-id-144287Thank you Mike. I would need "FP" to be found in "abcFPef" but not "Fp abc", case sensitive, and as parts of A. With your suggestion I have an Error509 when I enter ,one,".*"&A2&".*", as the criterion, or not finding it at all. Joes formula finds parts A2 as a part of A. But somehow the same formula with sumifs gives 0 in the real ods which I need to work with even if the scenario is completely the same as in the attached example file and some rows contain A2.Sun, 28 Jan 2018 14:06:34 +0100https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?comment=144287#post-id-144287Comment by inJesus for <p><code>=SUMPRODUCT(B4:B9;NOT(ISERROR(SEARCH($A$2;A4:A9)));D4:D9=1)</code></p>
<p><code>=SUMPRODUCT(one.B4:B9;NOT(ISERROR(SEARCH($A$2;one.A4:A9)));one.D4:D9=1)</code></p>
https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?comment=144290#post-id-144290The sumproduct formula is working in my real file. But what seems strange to me is that I do not want to multiply but sum only. https://help.libreoffice.org/Calc/Array_Functions#SUMPRODUCTSun, 28 Jan 2018 14:37:06 +0100https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?comment=144290#post-id-144290Comment by inJesus for <p><code>=SUMPRODUCT(B4:B9;NOT(ISERROR(SEARCH($A$2;A4:A9)));D4:D9=1)</code></p>
<p><code>=SUMPRODUCT(one.B4:B9;NOT(ISERROR(SEARCH($A$2;one.A4:A9)));one.D4:D9=1)</code></p>
https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?comment=144294#post-id-144294I need case sensitivity and activated it in the settings already. Then I [tried to use](https://ask.libreoffice.org/en/question/33989/case-sensitivity-using-calc-countif-function/) [EXACT](https://help.libreoffice.org/Common/Calculate#Case_sensitive) with SUMPRODUCT
but this brings error messages. How to have FP found but not Fp or fp ... with SUMPRODUCT?Sun, 28 Jan 2018 15:21:06 +0100https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?comment=144294#post-id-144294Comment by Mike Kaganski for <p><code>=SUMPRODUCT(B4:B9;NOT(ISERROR(SEARCH($A$2;A4:A9)));D4:D9=1)</code></p>
<p><code>=SUMPRODUCT(one.B4:B9;NOT(ISERROR(SEARCH($A$2;one.A4:A9)));one.D4:D9=1)</code></p>
https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?comment=144298#post-id-144298Replace [`SEARCH`](https://helponline.libreoffice.org/5.4/en-US/text/scalc/01/04060110.html?DbPAR=CALC#bm_id3151005) (case-insensitive) with [`FIND`](https://helponline.libreoffice.org/5.4/en-US/text/scalc/01/04060110.html?DbPAR=CALC#bm_id3152589) (case-sensitive). It is all in help.
Wrt "I do not want to multiply but sum only" - you are creating sum of products, each of them being one of value in the table multiplied by 1 or 0, depending on if it matches the criteria.Sun, 28 Jan 2018 15:57:53 +0100https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/?comment=144298#post-id-144298