| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning multiple values that are NOT in a single column or row
Hi all,
I am trying to keep track of various types of tomato seedlings on growing trays. Each tray is divided into eight sections and each section will have a certain number of seedlings in it. However the data are not contiguous, but instead are laid out in such a way so as to approximate everything's position in the tray. The data are always arranged with the number above and the type in the row below. A typical example might look something like this: 4 3 Big Boy Early Girl 4 4 Roma Big Boy 2 3 Early Girl Beefsteak 4 4 Big Boy Roma My questions is, is there a way to do a dynamic sum of each type? So, in the example above, there would ultimately be a list that looks like: 3 Beefsteak 12 Big Boy 5 Early Girl 8 Roma The fact that the data types are not separated into discrete columns and rows have hampered my efforts to figure this out so far. How would I do this? Any help would be greatly appreciated. I'm using Excel 2003. - Chris |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning multiple values that are NOT in a single column or row
Assume your data in A1:B11
Criteria in D2 holds Beefsteak, D3 holds Big Boy.... In E2: =SUMPRODUCT(--($A$2:$B$11=D2),$A$1:$B$10) copy down "crispino" wrote: Hi all, I am trying to keep track of various types of tomato seedlings on growing trays. Each tray is divided into eight sections and each section will have a certain number of seedlings in it. However the data are not contiguous, but instead are laid out in such a way so as to approximate everything's position in the tray. The data are always arranged with the number above and the type in the row below. A typical example might look something like this: 4 3 Big Boy Early Girl 4 4 Roma Big Boy 2 3 Early Girl Beefsteak 4 4 Big Boy Roma My questions is, is there a way to do a dynamic sum of each type? So, in the example above, there would ultimately be a list that looks like: 3 Beefsteak 12 Big Boy 5 Early Girl 8 Roma The fact that the data types are not separated into discrete columns and rows have hampered my efforts to figure this out so far. How would I do this? Any help would be greatly appreciated. I'm using Excel 2003. - Chris |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning multiple values that are NOT in a single column or r
OH YEAH THAT'S THE STUFF
Awesome! Thanks so much! But just out of curiosity . . . I'm not familiar with that formula (SUMPRODUCT). In looking it up in Excel help, I don't see the two dashes ever used, but in a lot of peoples' responses on these forums, they seem to get used a lot. And I note that when I take them out of the formula you gave me for my example, it doesn't work anymore. What do they do? Thanks again for the help, this is great. - Chris "Teethless mama" wrote: Assume your data in A1:B11 Criteria in D2 holds Beefsteak, D3 holds Big Boy.... In E2: =SUMPRODUCT(--($A$2:$B$11=D2),$A$1:$B$10) copy down "crispino" wrote: Hi all, I am trying to keep track of various types of tomato seedlings on growing trays. Each tray is divided into eight sections and each section will have a certain number of seedlings in it. However the data are not contiguous, but instead are laid out in such a way so as to approximate everything's position in the tray. The data are always arranged with the number above and the type in the row below. A typical example might look something like this: 4 3 Big Boy Early Girl 4 4 Roma Big Boy 2 3 Early Girl Beefsteak 4 4 Big Boy Roma My questions is, is there a way to do a dynamic sum of each type? So, in the example above, there would ultimately be a list that looks like: 3 Beefsteak 12 Big Boy 5 Early Girl 8 Roma The fact that the data types are not separated into discrete columns and rows have hampered my efforts to figure this out so far. How would I do this? Any help would be greatly appreciated. I'm using Excel 2003. - Chris |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning multiple values that are NOT in a single column or r
You're Welcome!
"crispino" wrote: OH YEAH THAT'S THE STUFF Awesome! Thanks so much! But just out of curiosity . . . I'm not familiar with that formula (SUMPRODUCT). In looking it up in Excel help, I don't see the two dashes ever used, but in a lot of peoples' responses on these forums, they seem to get used a lot. And I note that when I take them out of the formula you gave me for my example, it doesn't work anymore. What do they do? Thanks again for the help, this is great. - Chris "Teethless mama" wrote: Assume your data in A1:B11 Criteria in D2 holds Beefsteak, D3 holds Big Boy.... In E2: =SUMPRODUCT(--($A$2:$B$11=D2),$A$1:$B$10) copy down "crispino" wrote: Hi all, I am trying to keep track of various types of tomato seedlings on growing trays. Each tray is divided into eight sections and each section will have a certain number of seedlings in it. However the data are not contiguous, but instead are laid out in such a way so as to approximate everything's position in the tray. The data are always arranged with the number above and the type in the row below. A typical example might look something like this: 4 3 Big Boy Early Girl 4 4 Roma Big Boy 2 3 Early Girl Beefsteak 4 4 Big Boy Roma My questions is, is there a way to do a dynamic sum of each type? So, in the example above, there would ultimately be a list that looks like: 3 Beefsteak 12 Big Boy 5 Early Girl 8 Roma The fact that the data types are not separated into discrete columns and rows have hampered my efforts to figure this out so far. How would I do this? Any help would be greatly appreciated. I'm using Excel 2003. - Chris |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning multiple values that are NOT in a single column or r
Hey Teethless mama -
Just wanted to repeat my section from my previous post - guess you didn't see it. I'm just wondering what the double dash part of the SUMPRODUCT function does? I can't find an explanation in Excel help. - Chris "Teethless mama" wrote: You're Welcome! "crispino" wrote: OH YEAH THAT'S THE STUFF Awesome! Thanks so much! But just out of curiosity . . . I'm not familiar with that formula (SUMPRODUCT). In looking it up in Excel help, I don't see the two dashes ever used, but in a lot of peoples' responses on these forums, they seem to get used a lot. And I note that when I take them out of the formula you gave me for my example, it doesn't work anymore. What do they do? Thanks again for the help, this is great. - Chris "Teethless mama" wrote: Assume your data in A1:B11 Criteria in D2 holds Beefsteak, D3 holds Big Boy.... In E2: =SUMPRODUCT(--($A$2:$B$11=D2),$A$1:$B$10) copy down "crispino" wrote: Hi all, I am trying to keep track of various types of tomato seedlings on growing trays. Each tray is divided into eight sections and each section will have a certain number of seedlings in it. However the data are not contiguous, but instead are laid out in such a way so as to approximate everything's position in the tray. The data are always arranged with the number above and the type in the row below. A typical example might look something like this: 4 3 Big Boy Early Girl 4 4 Roma Big Boy 2 3 Early Girl Beefsteak 4 4 Big Boy Roma My questions is, is there a way to do a dynamic sum of each type? So, in the example above, there would ultimately be a list that looks like: 3 Beefsteak 12 Big Boy 5 Early Girl 8 Roma The fact that the data types are not separated into discrete columns and rows have hampered my efforts to figure this out so far. How would I do this? Any help would be greatly appreciated. I'm using Excel 2003. - Chris |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning multiple values that are NOT in a single column or r
See this:
http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "crispino" wrote in message ... Hey Teethless mama - Just wanted to repeat my section from my previous post - guess you didn't see it. I'm just wondering what the double dash part of the SUMPRODUCT function does? I can't find an explanation in Excel help. - Chris "Teethless mama" wrote: You're Welcome! "crispino" wrote: OH YEAH THAT'S THE STUFF Awesome! Thanks so much! But just out of curiosity . . . I'm not familiar with that formula (SUMPRODUCT). In looking it up in Excel help, I don't see the two dashes ever used, but in a lot of peoples' responses on these forums, they seem to get used a lot. And I note that when I take them out of the formula you gave me for my example, it doesn't work anymore. What do they do? Thanks again for the help, this is great. - Chris "Teethless mama" wrote: Assume your data in A1:B11 Criteria in D2 holds Beefsteak, D3 holds Big Boy.... In E2: =SUMPRODUCT(--($A$2:$B$11=D2),$A$1:$B$10) copy down "crispino" wrote: Hi all, I am trying to keep track of various types of tomato seedlings on growing trays. Each tray is divided into eight sections and each section will have a certain number of seedlings in it. However the data are not contiguous, but instead are laid out in such a way so as to approximate everything's position in the tray. The data are always arranged with the number above and the type in the row below. A typical example might look something like this: 4 3 Big Boy Early Girl 4 4 Roma Big Boy 2 3 Early Girl Beefsteak 4 4 Big Boy Roma My questions is, is there a way to do a dynamic sum of each type? So, in the example above, there would ultimately be a list that looks like: 3 Beefsteak 12 Big Boy 5 Early Girl 8 Roma The fact that the data types are not separated into discrete columns and rows have hampered my efforts to figure this out so far. How would I do this? Any help would be greatly appreciated. I'm using Excel 2003. - Chris |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning multiple values that are NOT in a single column or r
Hey there,
I'm getting a 404 error when I try to go to that page. Can you check the link? Thanks, Chris "T. Valko" wrote: See this: http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "crispino" wrote in message ... Hey Teethless mama - Just wanted to repeat my section from my previous post - guess you didn't see it. I'm just wondering what the double dash part of the SUMPRODUCT function does? I can't find an explanation in Excel help. - Chris "Teethless mama" wrote: You're Welcome! "crispino" wrote: OH YEAH THAT'S THE STUFF Awesome! Thanks so much! But just out of curiosity . . . I'm not familiar with that formula (SUMPRODUCT). In looking it up in Excel help, I don't see the two dashes ever used, but in a lot of peoples' responses on these forums, they seem to get used a lot. And I note that when I take them out of the formula you gave me for my example, it doesn't work anymore. What do they do? Thanks again for the help, this is great. - Chris "Teethless mama" wrote: Assume your data in A1:B11 Criteria in D2 holds Beefsteak, D3 holds Big Boy.... In E2: =SUMPRODUCT(--($A$2:$B$11=D2),$A$1:$B$10) copy down "crispino" wrote: Hi all, I am trying to keep track of various types of tomato seedlings on growing trays. Each tray is divided into eight sections and each section will have a certain number of seedlings in it. However the data are not contiguous, but instead are laid out in such a way so as to approximate everything's position in the tray. The data are always arranged with the number above and the type in the row below. A typical example might look something like this: 4 3 Big Boy Early Girl 4 4 Roma Big Boy 2 3 Early Girl Beefsteak 4 4 Big Boy Roma My questions is, is there a way to do a dynamic sum of each type? So, in the example above, there would ultimately be a list that looks like: 3 Beefsteak 12 Big Boy 5 Early Girl 8 Roma The fact that the data types are not separated into discrete columns and rows have hampered my efforts to figure this out so far. How would I do this? Any help would be greatly appreciated. I'm using Excel 2003. - Chris |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning multiple values that are NOT in a single column or r
I'm getting a 404 error
Me too! Sorry about that. That's a very well known and popular site. Must be some unexpected technical difficulty! Here's another link (tested this one!): http://mcgimpsey.com/excel/formulae/doubleneg.html -- Biff Microsoft Excel MVP "crispino" wrote in message ... Hey there, I'm getting a 404 error when I try to go to that page. Can you check the link? Thanks, Chris "T. Valko" wrote: See this: http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "crispino" wrote in message ... Hey Teethless mama - Just wanted to repeat my section from my previous post - guess you didn't see it. I'm just wondering what the double dash part of the SUMPRODUCT function does? I can't find an explanation in Excel help. - Chris "Teethless mama" wrote: You're Welcome! "crispino" wrote: OH YEAH THAT'S THE STUFF Awesome! Thanks so much! But just out of curiosity . . . I'm not familiar with that formula (SUMPRODUCT). In looking it up in Excel help, I don't see the two dashes ever used, but in a lot of peoples' responses on these forums, they seem to get used a lot. And I note that when I take them out of the formula you gave me for my example, it doesn't work anymore. What do they do? Thanks again for the help, this is great. - Chris "Teethless mama" wrote: Assume your data in A1:B11 Criteria in D2 holds Beefsteak, D3 holds Big Boy.... In E2: =SUMPRODUCT(--($A$2:$B$11=D2),$A$1:$B$10) copy down "crispino" wrote: Hi all, I am trying to keep track of various types of tomato seedlings on growing trays. Each tray is divided into eight sections and each section will have a certain number of seedlings in it. However the data are not contiguous, but instead are laid out in such a way so as to approximate everything's position in the tray. The data are always arranged with the number above and the type in the row below. A typical example might look something like this: 4 3 Big Boy Early Girl 4 4 Roma Big Boy 2 3 Early Girl Beefsteak 4 4 Big Boy Roma My questions is, is there a way to do a dynamic sum of each type? So, in the example above, there would ultimately be a list that looks like: 3 Beefsteak 12 Big Boy 5 Early Girl 8 Roma The fact that the data types are not separated into discrete columns and rows have hampered my efforts to figure this out so far. How would I do this? Any help would be greatly appreciated. I'm using Excel 2003. - Chris |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning multiple values that are NOT in a single column or r
That one worked and yes, that helps explain. So very clever. Thanks!
- Chris "T. Valko" wrote: I'm getting a 404 error Me too! Sorry about that. That's a very well known and popular site. Must be some unexpected technical difficulty! Here's another link (tested this one!): http://mcgimpsey.com/excel/formulae/doubleneg.html -- Biff Microsoft Excel MVP "crispino" wrote in message ... Hey there, I'm getting a 404 error when I try to go to that page. Can you check the link? Thanks, Chris "T. Valko" wrote: See this: http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "crispino" wrote in message ... Hey Teethless mama - Just wanted to repeat my section from my previous post - guess you didn't see it. I'm just wondering what the double dash part of the SUMPRODUCT function does? I can't find an explanation in Excel help. - Chris "Teethless mama" wrote: You're Welcome! "crispino" wrote: OH YEAH THAT'S THE STUFF Awesome! Thanks so much! But just out of curiosity . . . I'm not familiar with that formula (SUMPRODUCT). In looking it up in Excel help, I don't see the two dashes ever used, but in a lot of peoples' responses on these forums, they seem to get used a lot. And I note that when I take them out of the formula you gave me for my example, it doesn't work anymore. What do they do? Thanks again for the help, this is great. - Chris "Teethless mama" wrote: Assume your data in A1:B11 Criteria in D2 holds Beefsteak, D3 holds Big Boy.... In E2: =SUMPRODUCT(--($A$2:$B$11=D2),$A$1:$B$10) copy down "crispino" wrote: Hi all, I am trying to keep track of various types of tomato seedlings on growing trays. Each tray is divided into eight sections and each section will have a certain number of seedlings in it. However the data are not contiguous, but instead are laid out in such a way so as to approximate everything's position in the tray. The data are always arranged with the number above and the type in the row below. A typical example might look something like this: 4 3 Big Boy Early Girl 4 4 Roma Big Boy 2 3 Early Girl Beefsteak 4 4 Big Boy Roma My questions is, is there a way to do a dynamic sum of each type? So, in the example above, there would ultimately be a list that looks like: 3 Beefsteak 12 Big Boy 5 Early Girl 8 Roma The fact that the data types are not separated into discrete columns and rows have hampered my efforts to figure this out so far. How would I do this? Any help would be greatly appreciated. I'm using Excel 2003. - Chris |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLookup multiple values - sum returned values into single cell | Excel Worksheet Functions | |||
| Returning Multiple values | Excel Worksheet Functions | |||
| Looking up multiple values and returning one corresponding value | Excel Worksheet Functions | |||
| returning multiple cells for a single lookup | Excel Discussion (Misc queries) | |||
| Needing to return multiple values from single column | Excel Worksheet Functions | |||
Linear Mode
