One way to do this is to put in F1:
=SUMIF(B2:B5;E1;C2:C5)
If instead you put:
=SUMIF(B$2:B$5;E1;C$2:C$5)
you can even fill it down column F into F2, giving
=SUMIF(B$2:B$5;E2;C$2:C$5)
Another technique is to put in F1:
=SUMPRODUCT(B2:B5=E1;C2:C5)
or, again,
I have a spreadsheet with values that are categorized, for instance:
Name - Category - Value
lunch - food - 20
fuel - transportation - 100
dinner - food - 20
repairs - transportation - 200
Then I would like to have a sum of values for each category, such as:
food - 40
transportation - 300
Can
Bruno,
Assuming the headers are in row A and the data is in columns 1 thru use:
=sumid(c2:c5,food)
-Original Message-
From: Bruno Schneider boschnei...@gmail.com
To: users@global.libreoffice.org
Subject: [libreoffice-users] spreadsheet - sum one column based on the
value of other
Date
At 12:58 08/09/2013 -0300, Bruno Schneider wrote:
I have a spreadsheet with values that are categorized, for instance:
Name - Category - Value
lunch - food - 20
fuel - transportation - 100
dinner - food - 20
repairs - transportation - 200
Let's assume these values are in A1 to C5 - so your
Brian Barker wrote:
At 12:58 08/09/2013 -0300, Bruno Schneider wrote:
(omissions for brevity)
=SUMPRODUCT(B$2:B$5=E1;C$2:C$5)
so you can fill this down into F2.
How does this second method work? Well, the test of equality gives a
value which can be interpreted as a number: 1 for TRUE and 0
At 13:42 08/09/2013 -0400, Fred James wrote:
Brian Barker wrote:
=SUMPRODUCT(B$2:B$5=E1;C$2:C$5)
so you can fill this down into F2.
Typo found : switch the B's and C's so that the function reads:
=SUMPRODUCT(C$2:C$7,E1=B$2:B$7)
and it works.
Er, there is no typo, just some confusion on
Brian Barker wrote:
At 13:42 08/09/2013 -0400, Fred James wrote:
Brian Barker wrote:
=SUMPRODUCT(B$2:B$5=E1;C$2:C$5)
so you can fill this down into F2.
Typo found : switch the B's and C's so that the function reads:
=SUMPRODUCT(C$2:C$7,E1=B$2:B$7)
and it works.
Er, there is no typo,
Hi all:
Why not use the pilot table (Dinamic table) ? If you don't know How ? ,
let me to know and I will try to help you.
Regards,
Jorge RodrÃguez
El dom, 08-09-2013 a las 17:29 +0100, Brian Barker escribió:
Name - Category - Value
lunch - food - 20
fuel - transportation - 100
At 16:19 08/09/2013 -0400, Fred James wrote:
Brian Barker wrote:
Er, there is no typo, just some confusion on your part. The
original formula works. You have changed the test from X = Y to Y
= X and the product X x Y into Y x X. As any elementary
mathematician knows, equality and