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