Re: [libreoffice-users] spreadsheet - sum one column based on the value of other
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. That solves it. Thank you. -- Bruno Schneider -- To unsubscribe e-mail to: users+unsubscr...@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
[libreoffice-users] spreadsheet - sum one column based on the value of other
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 anyone help with functions to make this per category sum? -- Bruno Schneider -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] spreadsheet - sum one column based on the value of other
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: Sun, 8 Sep 2013 12:58:03 -0300 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 anyone help with functions to make this per category sum? -- Bruno Schneider -- Jay Lozier jsloz...@gmail.com -- To unsubscribe e-mail to: users+unsubscr...@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
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+unsubscr...@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
Re: [libreoffice-users] spreadsheet - sum one column based on the value of other
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 for FALSE. Multiplying this by your value switches each value on or off in the sum. I trust this helps. Brian Barker 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. Thanks ... I did enjoy that, and I learned something! Regards Fred James -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] spreadsheet - sum one column based on the value of other
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 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 multiplication are commutative, so these are the same thing: x = y and y = x are the same test; and 2 x 3 and 3 x 2 are the same value! Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] spreadsheet - sum one column based on the value of other
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, 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 multiplication are commutative, so these are the same thing: x = y and y = x are the same test; and 2 x 3 and 3 x 2 are the same value! Brian Barker Sorry ... yes: x=y is the same as y=x, but when testing the original function the results were incorrect. Switching the b's and c's made the function return the correct results. Assumption (on my part): the first part (C$2:C$5) is the column of values to be summed the second part (E1=) is the value we want to match in the third part the third part (B$2:B$5) is the column/range of values to be matched by the second part ¿Sí? So if we used column B for the first part we would be trying to sum labels/text, and if we used C in the third part, we would be trying to match labels/text to numbers? Or did I miss something? In any case, I must thank you for pointing to this solution ... it is great ... I really enjoyed this. Regards Fred James -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] spreadsheet - sum one column based on the value of other
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 dinner - food - 20 repairs - transportation - 200 -- Atentamente, Jorge Rodríguez -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] spreadsheet - sum one column based on the value of other
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 multiplication are commutative, so these are the same thing: x = y and y = x are the same test; and 2 x 3 and 3 x 2 are the same value! Sorry ... yes: x=y is the same as y=x, but when testing the original function the results were incorrect. We cannot know how, of course - but you must have tested it incorrectly! (I tested it, too.) Switching the b's and c's made the function return the correct results. But you haven't just done that: the comparison is still between the Bs and the E (though reversed) and the product is still between the B/E switch and the Cs (though also reversed). Assumption (on my part): the first part (C$2:C$5) is the column of values to be summed the second part (E1=) is the value we want to match in the third part the third part (B$2:B$5) is the column/range of values to be matched by the second part Yes: this also works - but there is no need to modify the original formula. Your chosen order of parts is not necessary for the formula to work. I'm not finding fault with your formula, but you are - incorrectly - with mine! So if we used column B for the first part we would be trying to sum labels/text, and if we used C in the third part, we would be trying to match labels/text to numbers? No! In my original formula, the E value was still compared with the Bs and the product made between the resulting switch and the Cs. Or did I miss something? Obviously yes: please go back and read my version again! In any case, I must thank you for pointing to this solution ... it is great ... I really enjoyed this. Good-oh! I have to say that SUMPRODUCT() had not been the first thing I'd think of in situations such as this - but I picked up the powerful technique from experts on this and similar lists. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
