Ask Your Question
0

How to make a sum in B2 of all cells in B where the corresponding A contains A2 and D is 1.

asked 2018-01-25 22:00:19 +0200

inJesus gravatar image

updated 2018-01-25 22:08:08 +0200

Hi

I would be so happy to get this complete formula. Please use the example file.

Klaus

C:\fakepath\Example sumifs.ods

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2018-01-25 22:17:47 +0200

updated 2018-01-25 22:20:04 +0200

=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)

edit flag offensive delete link more

Comments

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)

joe_hayden gravatar imagejoe_hayden ( 2018-01-26 00:04:06 +0200 )edit

Thank you to both of you. Both answers seem to work here. Blessings, Klaus

inJesus gravatar imageinJesus ( 2018-01-26 06:44:01 +0200 )edit

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).

Mike Kaganski gravatar imageMike Kaganski ( 2018-01-26 07:14:27 +0200 )edit

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.

inJesus gravatar imageinJesus ( 2018-01-28 14:06:34 +0200 )edit

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. https://help.libreoffice.org/Calc/Arr...

inJesus gravatar imageinJesus ( 2018-01-28 14:37:06 +0200 )edit

I need case sensitivity and activated it in the settings already. Then I tried to useEXACT with SUMPRODUCT but this brings error messages. How to have FP found but not Fp or fp ... with SUMPRODUCT?

inJesus gravatar imageinJesus ( 2018-01-28 15:21:06 +0200 )edit
1

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-01-28 15:57:53 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-01-25 22:00:19 +0200

Seen: 57 times

Last updated: Jan 25 '18