SUM(IF()) not working

964 views Asked by At

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.

1

There are 1 answers

0
tohuwawohu On BEST ANSWER

For such a task, you don't need an array formula - it's a typical task for a pivot table. To use it:

  1. Add Column headers:

    enter image description here

  2. Select the "data table" (in my example: A1:B4);

  3. Menu Data -> Pivot Table -> Create...; confirm current selection as source:

    enter image description here

  4. 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:

    enter image description here

  5. Optionally, open more options, select "identify categories";

  6. Hit ok:

    enter image description here