If A5 says "Total", D5 adds D2:D5, otherwise, D5 multiplies with C5

[iaMyoU]

Thanks in advance for taking the time to help me.
I am brand new to excel :)

BWwZPUa.png
\1

Currently, this is all D5 (and all of D) does =C5*0.1*-1
I would like them all to continue to do that unless I have typed Total into corresponding A.
If I have typed Total into A5, I want it to instead, change it's job and add all the numbers above it, together.


Hope this makes sense.

Thanks ^_^

[Pete_UK]

Some of us can't see image files on this Forum - please attach a sample Excel workbook instead.

Pete

[pjwhitfield]

Quick clarification, are there always 4items above or could Total appear anywhere in Column A? ie could you then have A7:A20 with items and A21 containing Total?

[iaMyoU]

Here you go Pete, my apologies :)
example-iaMyoU.xlsx

[hr]

Quick clarification, are there always 4items above or could Total appear anywhere in Column A? ie could you then have A7:A20 with items and A21 containing Total?

I could type Total anywhere in Column A. As a result, it'll turn blue automatically, and preferably it's corresponding D Column will add all of D instead of doing math with its C.

Thanks :)

[Pete_UK]

Put this formula in cell D2:

=IF(A2="Total",SUM(D$1:D1),C2*0.1*-1)

then copy the formula down.

Hope this helps.

Pete

[Pete_UK]

Further to my earlier answer, you should also put this formula in E2:

=IF(A2="Total",SUM(E$1:E1),C2*0.03*-1+0.3)

and this one in G2:

=IF(A2="Total",SUM(C$1:F1),SUM(C2:F2))

and copy both formulae down.

Hope this helps.

Pete

[iaMyoU]

you have no idea how much I appreciate your time. It has saved me possible HOURS or longer of research! I gave you a reputation ^_^
This question can be closed :)

[Pete_UK]

Well, thanks for feeding back - glad to help.

Pete