Re: [libreoffice-users] spreadsheet - sum one column based on the value of other

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 values are in rows 2 to 5.

> Then I would like to have a sum of values for each category, such as:
>
> food - 40
> transportation - 300

Let's put these two categories in column E - in E1 and E2.

> Can anyone help with functions to make this "per category sum"?

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,
=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 for 
FALSE.  Multiplying this by your "value" switches each value on or 
off in the sum.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted