Hi
I would be so happy to get this complete formula. Please use the example file.
Klaus
=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)
Slightly confused about what is being asked, though different way of doing the same thing =SUMIFS(B4:B9,A4:A9,A2,D4:D9,1)
Thank you to both of you. Both answers seem to work here. Blessings, Klaus
Hmmm… 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).
Thank 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.
The 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. Array Functions - LibreOffice Help
I need case sensitivity and activated it in the settings already. Then I tried to use EXACT with SUMPRODUCT
but this brings error messages. How to have FP found but not Fp or fp … with SUMPRODUCT?
Replace SEARCH (case-insensitive) with FIND (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.