Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Its a toughie, not sure if its possible.
I would like to know if its possible to create a sum total based
on categories selected in other cells. For example, I have 3 categories: First, Additional & replacement. First funnily enough is always the first number to be stated in the list, then there can be any number of Additionals on top of that. Replacements however override everything that has gone before it and effectively reset the sum to what the Replacement is worth, Additionals can also be added on top of these Replacements. Can I get a sum to intelligently see what categories are added to a list as they are added and calculate the equivalent totals? Eg First 1 Add 1 Sum Shows 2 First 1 Add 1 Replacement 24 Sum shows 24 If anyone can understand what im trying to create and help me out that would be fantastic. Many thanks in advance, Rikki P.S. Is this in the correct section? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Its a toughie, not sure if its possible.
Hi
If I understand you right, this is what you need. In B1 "first value" in B2 "add value" in B3 "Replace value" in B4 "add to replace value" Formula in B5: =IF(B3 0,SUM(B3:B4),SUM(B1:B2)) Hopes it helps Regards, Per "Chiccada" skrev i meddelelsen ... I would like to know if its possible to create a sum total based on categories selected in other cells. For example, I have 3 categories: First, Additional & replacement. First funnily enough is always the first number to be stated in the list, then there can be any number of Additionals on top of that. Replacements however override everything that has gone before it and effectively reset the sum to what the Replacement is worth, Additionals can also be added on top of these Replacements. Can I get a sum to intelligently see what categories are added to a list as they are added and calculate the equivalent totals? Eg First 1 Add 1 Sum Shows 2 First 1 Add 1 Replacement 24 Sum shows 24 If anyone can understand what im trying to create and help me out that would be fantastic. Many thanks in advance, Rikki P.S. Is this in the correct section? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Its a toughie, not sure if its possible.
Some questions... Is there only one First in the list? If there is a
Replacement, is it always the only item in the list and is it always the last item in the list? If the answer to either of these is no, then please provide a more comprehensive example showing the maximum possible interactions. Also, I presume that the First, Add and Replacement descriptions are in one column and the values are in another column? Rick "Chiccada" wrote in message ... I would like to know if its possible to create a sum total based on categories selected in other cells. For example, I have 3 categories: First, Additional & replacement. First funnily enough is always the first number to be stated in the list, then there can be any number of Additionals on top of that. Replacements however override everything that has gone before it and effectively reset the sum to what the Replacement is worth, Additionals can also be added on top of these Replacements. Can I get a sum to intelligently see what categories are added to a list as they are added and calculate the equivalent totals? Eg First 1 Add 1 Sum Shows 2 First 1 Add 1 Replacement 24 Sum shows 24 If anyone can understand what im trying to create and help me out that would be fantastic. Many thanks in advance, Rikki P.S. Is this in the correct section? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Its a toughie, not sure if its possible.
Hi Chiccada,
The following assumes that the second row is the first entry, and subsequent rows are in ascending chronological order; only one entry per moment in time. The First Row to Sum needs to be determined. If there is no replacement data, then the default will be to start with row 2 (first row after the column heads). In the sample below, Row column formula: =row() First Row to Sum Test formula: =IF(Category="Replacement",Row,2) Row Values Category First Row to Sum Test 2 600 First 2 3 400 Additional 2 4 200 Replacement 4 5 900 Additional 2 6 300 Replacement 6 7 500 Additional 2 {=SUM(IF(Row=MAX(First_Row_to_Sum_Test),Values))} This is an array formula. Copy the formula without the curly brackets, enter the formula by first hold down the Ctrl+Shift buttons while subsequently pressing the Enter button and Excel will add the curly brackets. Thanks, Peggy "Chiccada" wrote: I would like to know if its possible to create a sum total based on categories selected in other cells. For example, I have 3 categories: First, Additional & replacement. First funnily enough is always the first number to be stated in the list, then there can be any number of Additionals on top of that. Replacements however override everything that has gone before it and effectively reset the sum to what the Replacement is worth, Additionals can also be added on top of these Replacements. Can I get a sum to intelligently see what categories are added to a list as they are added and calculate the equivalent totals? Eg First 1 Add 1 Sum Shows 2 First 1 Add 1 Replacement 24 Sum shows 24 If anyone can understand what im trying to create and help me out that would be fantastic. Many thanks in advance, Rikki P.S. Is this in the correct section? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
correlating data from different worksheets - toughie? | Excel Worksheet Functions |