|
How to sum selected values from a table?
Hello all,
I hope someone can help me with this problem: From a table, I want to summerize the value (column 2) of all posts that has a specific text (column 1). This text should be the same as in an unique cell. Exemple: Book 1 Record 2 Magazine 3 Book 4 Record 5 Book 6 Unique cell text: [book] Number of books: [11] What is the formula in the cell which shows the value 11? If you know a solution, just copy the formula in your reply (instead of trying to explain in writing, which might be rather tiring). Hopefully, I can figure out how to make it work. (I have been using Excel for several years, for my personal need.) Thanks and Regards Thomas Jedenfelt Tyresö (Stockholm, Sweden) |
How to sum selected values from a table?
Hej Thomas
One way: =SUMPRODUCT((A1:A100="Book")*B1:B100) -- Best Regards Leo Heuser Excel MVP Followup to newsgroup only please. "Thomas Jedenfelt" skrev i en meddelelse om... Hello all, I hope someone can help me with this problem: From a table, I want to summerize the value (column 2) of all posts that has a specific text (column 1). This text should be the same as in an unique cell. Exemple: Book 1 Record 2 Magazine 3 Book 4 Record 5 Book 6 Unique cell text: [book] Number of books: [11] What is the formula in the cell which shows the value 11? If you know a solution, just copy the formula in your reply (instead of trying to explain in writing, which might be rather tiring). Hopefully, I can figure out how to make it work. (I have been using Excel for several years, for my personal need.) Thanks and Regards Thomas Jedenfelt Tyresö (Stockholm, Sweden) |
How to sum selected values from a table?
Hej Leo,
Tack så mycket för ditt svar. Thank you very much for your formula. It was exactly what needed! You made my day!! Another problem came up: I would like to have the sum of 'Record' added to the sum of 'Book'. The total should then be 18 (11+2+5). I have tried with the function [OR], but without luck. Below formula works, but I'm sure it can be done in a more proper manner. =SUMPRODUCT((A1:A100="Book")*B1:B100)+SUMPRODUCT(( A1:A100="Record")*B1:B100) Thanks again. Hilsen Thomas Jedenfelt P.S. It's very nice to get help from a MVP (Microsoft Most Valuable Professional). "Leo Heuser" wrote in message ... Hej Thomas One way: =SUMPRODUCT((A1:A100="Book")*B1:B100) -- Best Regards Leo Heuser Excel MVP Followup to newsgroup only please. |
How to sum selected values from a table?
Hej igen, Thomas, og velbekomme.
Yes, there is a shorter way: =SUMPRODUCT((A1:A100={"Book","Record"})*B1:B100) or, if the names of the items to search for are in e.g. F1:G1 (Book in F1 and Record in G1) =SUMPRODUCT((A1:A100=F1:G1)*B1:B100) You can add items in H1, i1, J1 etc. and adjust the formula accordingly, e.g. =SUMPRODUCT((A1:A100=F1:J1)*B1:B100) -- Best Regards Leo Heuser Excel MVP Followup to newsgroup only please. "Thomas Jedenfelt" skrev i en meddelelse om... Hej Leo, Tack så mycket för ditt svar. Thank you very much for your formula. It was exactly what needed! You made my day!! Another problem came up: I would like to have the sum of 'Record' added to the sum of 'Book'. The total should then be 18 (11+2+5). I have tried with the function [OR], but without luck. Below formula works, but I'm sure it can be done in a more proper manner. =SUMPRODUCT((A1:A100="Book")*B1:B100)+SUMPRODUCT(( A1:A100="Record")*B1:B100) Thanks again. Hilsen Thomas Jedenfelt P.S. It's very nice to get help from a MVP (Microsoft Most Valuable Professional). "Leo Heuser" wrote in message ... Hej Thomas One way: =SUMPRODUCT((A1:A100="Book")*B1:B100) -- Best Regards Leo Heuser Excel MVP Followup to newsgroup only please. |
How to sum selected values from a table?
Hej igen Leo,
Thanks again for taking the time to help. Your shorter formula did not work. (I copied and pasted it.) =SUMPRODUCT((A1:A100={"Book","Record"})*B1:B100) The error pop-up said 'wrong formula'. I presume that curley brackets {} is for delimiting a matrix area. (I use PC Win98, Excel 97) It's okay, though. I can use the long formula (Message #3). Your formula =SUMPRODUCT((A1:A100=F1:G1)*B1:B100) for selection criteria (F1:G1) did work, but only within rows. Columns did _not_ work. For example (selection criteria cells F1:F2) =SUMPRODUCT((A1:A100=F1:F2)*B1:B100) If you (or anyone else) have a solution, that would be good. If not, that's okay. I probably can make a work-around, when the needed. Tack igen. Hilsen Thomas Jedenfelt "Leo Heuser" wrote in message ... Hej igen, Thomas, og velbekomme. Yes, there is a shorter way: =SUMPRODUCT((A1:A100={"Book","Record"})*B1:B100) or, if the names of the items to search for are in e.g. F1:G1 (Book in F1 and Record in G1) =SUMPRODUCT((A1:A100=F1:G1)*B1:B100) You can add items in H1, i1, J1 etc. and adjust the formula accordingly, e.g. =SUMPRODUCT((A1:A100=F1:J1)*B1:B100) -- Best Regards Leo Heuser Excel MVP |
How to sum selected values from a table?
Hej igen igen
"Thomas Jedenfelt" skrev i en meddelelse om... Hej igen Leo, Thanks again for taking the time to help. Your shorter formula did not work. (I copied and pasted it.) =SUMPRODUCT((A1:A100={"Book","Record"})*B1:B100) The error pop-up said 'wrong formula'. I presume that curley brackets {} is for delimiting a matrix area. Yes, it's a matrix of constants {"Book","Record"} Be aware that the delimiter (here comma) must be the localized equivalent, which in Sweden is semicolon, so changing {"Book","Record"} to {"Book";"Record"} should make the formula work. (I use PC Win98, Excel 97) It's okay, though. I can use the long formula (Message #3). Your formula =SUMPRODUCT((A1:A100=F1:G1)*B1:B100) for selection criteria (F1:G1) did work, but only within rows. Columns did _not_ work. For example (selection criteria cells F1:F2) =SUMPRODUCT((A1:A100=F1:F2)*B1:B100) In order to make it work for columns, you have to use the TRANSPOSE-function like this: =SUMPRODUCT((A1:A100=TRANSPOSE(F1:F2))*B1:B100) or =SUM((A1:A100=TRANSPOSE(F1:F2))*B1:B100) Both formulae must now be entered with <Shift<Ctrl<Enter, also if edited later. If done correctly, Excel will display the formula in the formula bar enclosed in curly brackets { }. Don't enter these brackets yourself. Using TRANSPOSE sets a maximum of 5461 items (F1:F5461), but that's probably not a problem in this context :-) --- Best Regards Leo Heuser Excel MVP If you (or anyone else) have a solution, that would be good. If not, that's okay. I probably can make a work-around, when the needed. Tack igen. Hilsen Thomas Jedenfelt "Leo Heuser" wrote in message ... Hej igen, Thomas, og velbekomme. Yes, there is a shorter way: =SUMPRODUCT((A1:A100={"Book","Record"})*B1:B100) or, if the names of the items to search for are in e.g. F1:G1 (Book in F1 and Record in G1) =SUMPRODUCT((A1:A100=F1:G1)*B1:B100) You can add items in H1, i1, J1 etc. and adjust the formula accordingly, e.g. =SUMPRODUCT((A1:A100=F1:J1)*B1:B100) -- Best Regards Leo Heuser Excel MVP |
How to sum selected values from a table?
Hej Leo,
Yes, I have a Swedish version of Excel 97, and changing the operator from comma (,) to semicolon (;) in the matrix of constants {"Book","Record"} did work. (I should have figured that out myself, as I have learned much about Excel formulae by trial and error <smile.) With your TRANSPOSE-function, I now have the option to have the selection criteria cells listed row by row (F1:F2), instead of column by column (F1:G1). =SUMPRODUCT((A1:A100=TRANSPOSE(F1:F2))*B1:B100) (It took a while to get the formulae to work, as I forgot how to enter this kind of formulae: <Shift<Ctrl<Enter, which you mentioned. <smile) I think I should mention how I am using the formulae you have helped me with. It's to get an overview of my economy. I have one sheet for transactions and another for summaries (overview). The formulae below are for summarizing transaction types. Without the formulae, I had to do some summarizing _manually_, with the risk of making errors. =PRODUKTSUMMA((Okt!D3:D43=D10)*Okt!E3:E43) =PRODUKTSUMMA((Okt!C3:C43={3020;3030;3040;3050})*O kt!F3:F43) To give something in return for all your help, here are four useful links: Google Labs http://labs.google.com/ I recommend 'Search by Location' and 'News Alerts'. timeanddate.com http://www.timeanddate.com/worldclock/city.html?n=69 World Clock and Calendar, by a Norwegian guy. All Music Guide (AMG) http://www.allmusic.com/ Artist biographies and discographies. Netcraft http://www.netcraft.com/ Platform detection. Thank you very much! Hilsen Thomas Jedenfelt, a more content guy than prior to 23rd Oct. <smile "Leo Heuser" wrote in message ... Hej igen igen Your shorter formula did not work. (I copied and pasted it.) =SUMPRODUCT((A1:A100={"Book","Record"})*B1:B100) The error pop-up said 'wrong formula'. I presume that curley brackets {} is for delimiting a matrix area. Yes, it's a matrix of constants {"Book","Record"} Be aware that the delimiter (here comma) must be the localized equivalent, which in Sweden is semicolon, so changing {"Book","Record"} to {"Book";"Record"} should make the formula work. Your formula =SUMPRODUCT((A1:A100=F1:G1)*B1:B100) for selection criteria (F1:G1) did work, but only within rows. Columns did _not_ work. For example (selection criteria cells F1:F2) =SUMPRODUCT((A1:A100=F1:F2)*B1:B100) In order to make it work for columns, you have to use the TRANSPOSE-function like this: =SUMPRODUCT((A1:A100=TRANSPOSE(F1:F2))*B1:B100) or =SUM((A1:A100=TRANSPOSE(F1:F2))*B1:B100) Both formulae must now be entered with <Shift<Ctrl<Enter, also if edited later. If done correctly, Excel will display the formula in the formula bar enclosed in curly brackets { }. Don't enter these brackets yourself. Using TRANSPOSE sets a maximum of 5461 items (F1:F5461), but that's probably not a problem in this context :-) --- Best Regards Leo Heuser Excel MVP |
How to sum selected values from a table?
Hej Thomas
You're welcome, and thanks for the feedback. I'll give the links a try :-) Med venlig hilsen Leo Heuser "Thomas Jedenfelt" skrev i en meddelelse om... Hej Leo, Yes, I have a Swedish version of Excel 97, and changing the operator from comma (,) to semicolon (;) in the matrix of constants {"Book","Record"} did work. (I should have figured that out myself, as I have learned much about Excel formulae by trial and error <smile.) With your TRANSPOSE-function, I now have the option to have the selection criteria cells listed row by row (F1:F2), instead of column by column (F1:G1). =SUMPRODUCT((A1:A100=TRANSPOSE(F1:F2))*B1:B100) (It took a while to get the formulae to work, as I forgot how to enter this kind of formulae: <Shift<Ctrl<Enter, which you mentioned. <smile) I think I should mention how I am using the formulae you have helped me with. It's to get an overview of my economy. I have one sheet for transactions and another for summaries (overview). The formulae below are for summarizing transaction types. Without the formulae, I had to do some summarizing _manually_, with the risk of making errors. =PRODUKTSUMMA((Okt!D3:D43=D10)*Okt!E3:E43) =PRODUKTSUMMA((Okt!C3:C43={3020;3030;3040;3050})*O kt!F3:F43) To give something in return for all your help, here are four useful links: Google Labs http://labs.google.com/ I recommend 'Search by Location' and 'News Alerts'. timeanddate.com http://www.timeanddate.com/worldclock/city.html?n=69 World Clock and Calendar, by a Norwegian guy. All Music Guide (AMG) http://www.allmusic.com/ Artist biographies and discographies. Netcraft http://www.netcraft.com/ Platform detection. Thank you very much! Hilsen Thomas Jedenfelt, a more content guy than prior to 23rd Oct. <smile "Leo Heuser" wrote in message ... Hej igen igen Your shorter formula did not work. (I copied and pasted it.) =SUMPRODUCT((A1:A100={"Book","Record"})*B1:B100) The error pop-up said 'wrong formula'. I presume that curley brackets {} is for delimiting a matrix area. Yes, it's a matrix of constants {"Book","Record"} Be aware that the delimiter (here comma) must be the localized equivalent, which in Sweden is semicolon, so changing {"Book","Record"} to {"Book";"Record"} should make the formula work. Your formula =SUMPRODUCT((A1:A100=F1:G1)*B1:B100) for selection criteria (F1:G1) did work, but only within rows. Columns did _not_ work. For example (selection criteria cells F1:F2) =SUMPRODUCT((A1:A100=F1:F2)*B1:B100) In order to make it work for columns, you have to use the TRANSPOSE-function like this: =SUMPRODUCT((A1:A100=TRANSPOSE(F1:F2))*B1:B100) or =SUM((A1:A100=TRANSPOSE(F1:F2))*B1:B100) Both formulae must now be entered with <Shift<Ctrl<Enter, also if edited later. If done correctly, Excel will display the formula in the formula bar enclosed in curly brackets { }. Don't enter these brackets yourself. Using TRANSPOSE sets a maximum of 5461 items (F1:F5461), but that's probably not a problem in this context :-) --- Best Regards Leo Heuser Excel MVP |
| All times are GMT +1. The time now is 02:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com