Its a toughie, not sure if its possible.

[Chiccada]

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?

 

[Per Jessen]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[pshepard]

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