How to SUMIF that tests if length of cell is 1 [closed]

asked 2017-02-28 12:01:57 +0100

migueldealmeida
29 ●7 ●10 ●15

Consider the following:

image description

What is the formula that "sums column C if the length of the corresponding cell in column A is =1" ?

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-09-26 11:30:03.437325

add a comment

2

answered 2017-02-28 14:00:54 +0100

karolus

5584 ●17 ●40

Hallo

Solution with the Swiss-Army-Knife in terms of functions:
=SUMPRODUCT(LEN(A1:A4)=1, C1:C4)

edit flag offensive delete link

Comments

Yes. Much better imo.

Lupp ( 2017-02-28 14:37:28 +0100 )edit

Agree. This may be also faster.

Mike Kaganski ( 2017-03-01 09:40:37 +0100 )edit

Aha! I agree, this was what I was looking for! Thanks :)

migueldealmeida ( 2017-03-01 15:36:40 +0100 )edit

add a comment

0

answered 2017-02-28 12:26:12 +0100

Mike Kaganski

17495 ●12 ●72 ●168 https://www.collaborao...

updated 2017-02-28 12:26:40 +0100

If you use wildcards in formulas (default for recent LO versions), then you may use "=?" as criteria. If you use regular expressions, your criteria will be "=.". See Tools->Options->LibreOffice Calc->Calculate for settings that control using regexes/wildcards in formulas.

edit flag offensive delete link

Comments

I have wildcards on. Not sure how to write it. Do you mean =SUMIF(A1:A4,LEN(=?)=1,C1:C4) ? This formula doesn't work.

migueldealmeida ( 2017-02-28 12:36:48 +0100 )edit

=SUMIF(A1:A4,"=?",C1:C4)

Mike Kaganski ( 2017-02-28 12:37:39 +0100 )edit

1

Note that "=?" depends on the Tools-Options-Calc-Calculate setting "Search criteria = and <> must apply to whole cells", if that is disabled the search matches any cell content of at least one character, not just one character.

erAck ( 2017-02-28 13:04:59 +0100 )edit

The = inside the criteria-expression is optional both (wildcard|regEx) works without it.
anyway I would prefer a solution without dependencies of weird settings…see my Answer

karolus ( 2017-02-28 13:53:16 +0100 )edit

add a comment

How to SUMIF that tests if length of cell is 1 [closed]

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-26 11:30:03.437325

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

How to SUMIF that tests if length of cell is 1 [closed] edit

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-09-26 11:30:03.437325

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

How to SUMIF that tests if length of cell is 1 [closed]

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-26 11:30:03.437325