I want to calculate the sum of a column but only if the value of another column on that row has a certain value:
---------
| A | 1 |
| A | 2 |
| B | 5 |
---------
This should yield: A=3 and B=5.
After some attempts, I copied the following from their website
=SUM((C3:C5="red")*(B3:B5="big")*D3:D5)
Which doesn't work: whatever I try, I always seem to get #VALUE!
... Any workarounds to accomplish this?
This is LibreOffice: 4.4.2.2. + I've checked the formatting of the cells.
For such a task, you don't need an array formula - it's a typical task for a pivot table. To use it:
Add Column headers:
Select the "data table" (in my example:
A1:B4
);Menu
Data
->Pivot Table
->Create...
; confirm current selection as source:Drag Category to column (or row) fields, and Count to Data fields. LO offers to sum the count, you can select other functions to apply on the data fields:
Optionally, open more options, select "identify categories";
Hit ok: