If cell contains 'text' then another cell will automatically contain 'SUM'

[AngieBramley]

Hi All,

I’d like to create a formula that would mean………
If the word ‘float’ is inserted into a cell in column C then………….
Column E & F will automatically add up the totals above and…………
Column G will be the sum of E-F

(an example of the end result being in row 7)

any ideas?

Thanks

Edit: I forgot to add that also if a cell does contain the word 'float' then it automatcally turns grey too :S Thanks

[arlu1201]

Will there be multiple such occurrences in your data or just 1?

[AngieBramley]

There will be multiple,

So a total per 'work shift' then one at the end of the day, or week etc.

[arlu1201]

You mean you need a total per work shift, then at the end of the day, week, etc?

Can you upload a sample containing such data?

[AngieBramley]

Sorry but I'm not sure I understand what it is you mean :S

I do not have a sample containing the data, only the worksheet which I've origianally attached to the initial post.

[arlu1201]

You said there will be multiple sections of data and on every occurrence of "Float", the required changes need to take place.

How will the data for the next day be displayed? Will there be blank rows between each day's data or will it just be continuous?

[Fotis1991]

Hi

If i understand correctly, you put manually the values in columns E & F??? If YES, then you can not use formulas in the same cells.. You can get your resulys in Column H, probably using 2 helper columns... Is this OK, for you?

[AngieBramley]

Hi
If i understand correctly, you put manually the values in columns E & F???

Yes, that's correct

If YES, then you can not use formulas in the same cells..

Oh ok , something new I've learned

You can get your resulys in Column H, probably using 2 helper columns... Is this OK, for you?

Making an extra column for them would be fine. As long as the 'SUM' and subsequently 'Bal' infor (column F) is there it's not important which column they're in.

[AngieBramley]

Oh sorry, I think i misunderstood what was asked. It appears my lack of excel knowledge and terminology has managed to confuse things I'll see if I can try to explain what I'm planning on doing.

The spreadsheet will be filled in as and when a purchase is made, or money goes in and out etc. At any time I'd like to be able to type the word 'float' in column C. This will then automatically make column E & F 'SUM' up the data above and Column G will give a reading of the balance (E-F).

Then I'd like to be able to carry on adding data underneath and, again at any point, type in the word 'float' which will bring up the total of the WHOLE column (even before the previuos 'float' was typed.

Does this make more sense? Excel really is a genuis tool, but a very confusing one!

[arlu1201]

So a couple of more questions -

1. You want the sums to be calculated as soon as you type "float" ?

2. At every instance of "Float", you want the sum of the whole column upto that row? And not the sum of data from the 1st "float" to the 2nd "float", right?

[AngieBramley]

1. You want the sums to be calculated as soon as you type "float" ?

Yes

2. At every instance of "Float", you want the sum of the whole column upto that row?

Yes

And not the sum of data from the 1st "float" to the 2nd "float"?

No, NOT the sum of data between the 'float'(s)

right?

Yep, that's right

[Fotis1991]

Try in G3 and copy down.

=IF(C3="Float",SUM($E$3:E3)-SUM($F$3:F3),"")

Is this, works for you?

[Jamesr571]

Hello,

When your adding in the floats & wanting it to sum up everything above, I am assuming you want the previous floats to not be included?

EG:

Line 1 - £10.00 in | £5.00 Out
Line 2 - £15.00 in | £10.00 Out
Line 3 - Float (Sum of Line 1 & 2)
Line 4 - £10.00 in | £5.00 Out
Line 5 - £15.00 in | £10.00 Out
Line 6 - Float (Sum of Line 1 & 2 & 4 & 5, ignoring the previous float).

[AngieBramley]

Is this, works for you?

Thank you

When your adding in the floats & wanting it to sum up everything above, I am assuming you want the previous floats to not be included?

EG:

Line 1 - £10.00 in | £5.00 Out
Line 2 - £15.00 in | £10.00 Out
Line 3 - Float (Sum of Line 1 & 2)
Line 4 - £10.00 in | £5.00 Out
Line 5 - £15.00 in | £10.00 Out
Line 6 - Float (Sum of Line 1 & 2 & 4 & 5, ignoring the previous float).

Yes that's correct