Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autogroup then sum - can it be done?
This is what I would like to do, if possible:
Sum the values of column B that correspond with a set of unique row entries in column A. The sum should go on the first of the unique set rows in Column C. But, for example... Column A: Apples Apples Apples Oranges Oranges Oranges Pears Column B: 2 3 2 2 2 2 2 Column C: 7 - - 6 - - 2 I found instructions on how to do something similar (http://tipsforspreadsheets.com/micro...ion_0027.html), but it requires that you type the different things in column A into the formula. I want excel to be able to group the things in column A automatically and then sum the corresponding column B numbers of that group. Why? Because there are 350 unique row values in Column A in my actual data. I just want to make one formula, or function, or whatever - not 350 of them. Is that possible? Or am I just wasting time when I could be autosumming each group manually instead of looking for an automatic way to do this? Unfortunately, autosum is really about the extent of my knowledge of formulas, functions or whatever-they-ares, so that's why I need some help. Thank you bunches! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autogroup then sum - can it be done?
Try Pivot table
"Jessica Robinson" wrote: This is what I would like to do, if possible: Sum the values of column B that correspond with a set of unique row entries in column A. The sum should go on the first of the unique set rows in Column C. But, for example... Column A: Apples Apples Apples Oranges Oranges Oranges Pears Column B: 2 3 2 2 2 2 2 Column C: 7 - - 6 - - 2 I found instructions on how to do something similar (http://tipsforspreadsheets.com/micro...ion_0027.html), but it requires that you type the different things in column A into the formula. I want excel to be able to group the things in column A automatically and then sum the corresponding column B numbers of that group. Why? Because there are 350 unique row values in Column A in my actual data. I just want to make one formula, or function, or whatever - not 350 of them. Is that possible? Or am I just wasting time when I could be autosumming each group manually instead of looking for an automatic way to do this? Unfortunately, autosum is really about the extent of my knowledge of formulas, functions or whatever-they-ares, so that's why I need some help. Thank you bunches! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autogroup then sum - can it be done?
Hi,
you may highlight the data and go to Data Subtotals. Supply the relevant inouts -- Regards, Ashish Mathur Microsoft Excel MVP "Jessica Robinson" <Jessica wrote in message ... This is what I would like to do, if possible: Sum the values of column B that correspond with a set of unique row entries in column A. The sum should go on the first of the unique set rows in Column C. But, for example... Column A: Apples Apples Apples Oranges Oranges Oranges Pears Column B: 2 3 2 2 2 2 2 Column C: 7 - - 6 - - 2 I found instructions on how to do something similar (http://tipsforspreadsheets.com/micro...ion_0027.html), but it requires that you type the different things in column A into the formula. I want excel to be able to group the things in column A automatically and then sum the corresponding column B numbers of that group. Why? Because there are 350 unique row values in Column A in my actual data. I just want to make one formula, or function, or whatever - not 350 of them. Is that possible? Or am I just wasting time when I could be autosumming each group manually instead of looking for an automatic way to do this? Unfortunately, autosum is really about the extent of my knowledge of formulas, functions or whatever-they-ares, so that's why I need some help. Thank you bunches! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autogroup then sum - can it be done?
John, this worked perfectly! Thank you so much.
~ Jessica "John" wrote: Hi Jessica If your product starts in A2, try this formula and copy down. =IF(A2=A1,"",SUMPRODUCT(--(A2:A10=A2),B2:B10)) HTH John "Jessica Robinson" <Jessica wrote in message ... This is what I would like to do, if possible: Sum the values of column B that correspond with a set of unique row entries in column A. The sum should go on the first of the unique set rows in Column C. But, for example... Column A: Apples Apples Apples Oranges Oranges Oranges Pears Column B: 2 3 2 2 2 2 2 Column C: 7 - - 6 - - 2 I found instructions on how to do something similar (http://tipsforspreadsheets.com/micro...ion_0027.html), but it requires that you type the different things in column A into the formula. I want excel to be able to group the things in column A automatically and then sum the corresponding column B numbers of that group. Why? Because there are 350 unique row values in Column A in my actual data. I just want to make one formula, or function, or whatever - not 350 of them. Is that possible? Or am I just wasting time when I could be autosumming each group manually instead of looking for an automatic way to do this? Unfortunately, autosum is really about the extent of my knowledge of formulas, functions or whatever-they-ares, so that's why I need some help. Thank you bunches! . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autogroup then sum - can it be done?
You're welcome.
Thanks for feeding back John "Jessica Robinson" wrote in message ... John, this worked perfectly! Thank you so much. ~ Jessica "John" wrote: Hi Jessica If your product starts in A2, try this formula and copy down. =IF(A2=A1,"",SUMPRODUCT(--(A2:A10=A2),B2:B10)) HTH John "Jessica Robinson" <Jessica wrote in message ... This is what I would like to do, if possible: Sum the values of column B that correspond with a set of unique row entries in column A. The sum should go on the first of the unique set rows in Column C. But, for example... Column A: Apples Apples Apples Oranges Oranges Oranges Pears Column B: 2 3 2 2 2 2 2 Column C: 7 - - 6 - - 2 I found instructions on how to do something similar (http://tipsforspreadsheets.com/micro...ion_0027.html), but it requires that you type the different things in column A into the formula. I want excel to be able to group the things in column A automatically and then sum the corresponding column B numbers of that group. Why? Because there are 350 unique row values in Column A in my actual data. I just want to make one formula, or function, or whatever - not 350 of them. Is that possible? Or am I just wasting time when I could be autosumming each group manually instead of looking for an automatic way to do this? Unfortunately, autosum is really about the extent of my knowledge of formulas, functions or whatever-they-ares, so that's why I need some help. Thank you bunches! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|