We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

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

inJesus gravatar image

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


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


C:\fakepath\Example sumifs.ods

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-27 16:16:51.566280

1 Answer

Sort by » oldest newest most voted

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

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



edit flag offensive delete link more



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

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

Question Tools

1 follower


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

Seen: 99 times

Last updated: Jan 25 '18