Ask Your Question
0

Summing a variable-valued column [closed]

asked 2013-02-09 23:10:52 +0100

dv gravatar image

I have a column of values (expenses, to be exact). The next column may have any of several flags, represented as letters. So it would look something like this:

$1,000 A

$1,105 D

$2,150

$5,213 D

I want the sum of values where the next column contains a given flag. I can do this by creating an intermediate column that contains either 0 or the flagged value (using an IF command), then summing that column.

Is there a way to do this without an intermediate column?

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 2015-10-21 15:40:48.464862

1 Answer

Sort by » oldest newest most voted
0

answered 2013-02-10 00:59:43 +0100

m.a.riosv gravatar image

For sure.

=SUMIF(FlagsColumn;Flag;ValuesColumn)

e.g. =SUMIF($B$2:$B$100;"A";$A$2)

Take a look in the LibreOffice help about the function.
p.d. Be sure the values to sum are numbers, not text representing numbers.

edit flag offensive delete link more

Comments

I could not get mariosv's formula to work if the flag is a blank cell. One work around is to sum all the cells and subtract the sum of all the non-blank key cells. Or insert a flag where none exists.

w_whalley gravatar imagew_whalley ( 2013-02-10 01:31:10 +0100 )edit

IMO is best do the things explicit, and do not let it to an interpretation. Although is possible to do it with =SUMPRODUCT(ISBLANK($B$2:$B$100);$A$2:$A$100). But is more complicate to use regular expression.

m.a.riosv gravatar imagem.a.riosv ( 2013-02-10 01:56:56 +0100 )edit

Question Tools

Stats

Asked: 2013-02-09 23:10:52 +0100

Seen: 461 times

Last updated: Feb 10 '13