Excel Whizzes...ASSEMBLE!

Bennie-Mac · 1 May 2013 at 16:32

Hey guys,

Im wondering if it is possible to create an "IF" statement in Excel which relates to the colour of the text used?

Image for reference

Basically what I am hoping to achieve is when a payment goes out, I change the text colour to green and then magic happens where it adds the amount to the "Total Fixed Outgoings" cell. For example the total fixed outgoings for May should currently be £615.24, not £1733.43 as not everything has come out of the account yet, only the green totals.

Is this possible? If so could you explain how in extra special "talking to a moron" terms, as im pretty terrible with Excel...

Alternatively if there are any easier or better options which achieve the same result, feel free to enlighten me.

Many thanks

One More Solo · 1 May 2013 at 16:39

Not sure if that is possible. I would insert a column to the side of each month that is just "Paid Y/N" then use the SUMIF function to add up all the ones that are Y.

So =SUMIF(B1:B100,Y,A1:A100)

In this instance. B1:B100 would be the column containing Y/N, A1:A100 would be the column containing the payment amounts, just amend as appropriate.

feriso · 1 May 2013 at 16:46

I tried something similar, but never got it to work so what I set up was an IF command which put a 1 in an adjacent column and then have a subsequent column where a sub total si your amout * the 1 (or 0 if it has not gone out)

Hatter The Mad · 1 May 2013 at 16:46

Can't be done without some VBA script/macro. I'd also suggest that this is a bit of an abuse of Excel and as you expand the use and function of your spreadsheet, will cause you bigger headaches later down the line. I like the suggestion above in terms of not redesigning the entire thing but achieving what you want.

Firestar_3x · 1 May 2013 at 16:50

One More Solo said:
Not sure if that is possible. I would insert a column to the side of each month that is just "Paid Y/N" then use the SUMIF function to add up all the ones that are Y.

So =SUMIF(B1:B100,Y,A1:A100)

In this instance. B1:B100 would be the column containing Y/N, A1:A100 would be the column containing the payment amounts, just amend as appropriate.

Yep, i'd prob go down that route.

neil3k · 1 May 2013 at 17:02

Conditional formatting is your friend here.

Firestar_3x · 1 May 2013 at 17:05

neil3k said:
Conditional formatting is your friend here.

I don't think you can do IF statements based on conditional formatting?

londonbairn · 1 May 2013 at 17:17

best route is to have a yes/no field to be honest as above, or 0 each field until it's paid

you cannot ref formatting in a formula; only in VBA whereby you can expose a cell's properties like background color etc, but that is way, way overkill here.

Bennie-Mac · 1 May 2013 at 17:22

Thanks for all the replies

As you can see i've added an extra column after May with the Y/N, in cell F22 i have the formula =SUMIF(G8:G19,Y,F8:F19) but the total is £0.00

Please note I have just typed in Y or N for each cell, am I supposed to be doing something different for this? From what I gather the formula is correct?

Hatter The Mad · 1 May 2013 at 17:24

Have you entered "£599" literally into the cells (which is not a number, it is a string) rather than entering 599 and telling Excel to format it as currency? That might be why.

londonbairn · 1 May 2013 at 17:24

criteria is "Y", not Y

Bennie-Mac · 1 May 2013 at 17:25

londonbairn said:
criteria is "Y", not Y

Bingo!

Great stuff guys, your help is much appreciated! Thanks a lot

Tomsk · 2 May 2013 at 10:01

Presumably you use some conditional formatting to turn the text green, so why don't you use the same condition to add to the total?

Also most of the outgoings look dependent on the date, so the date itself could be the trigger.

Hatter The Mad · 2 May 2013 at 10:13

Tomsk said:
Presumably you use some conditional formatting to turn the text green, so why don't you use the same condition to add to the total?

Also most of the outgoings look dependent on the date, so the date itself could be the trigger.

Conditional formatting doesn't have anything to do with formulae

Tomsk · 2 May 2013 at 14:05

Hatter The Mad said:
Conditional formatting doesn't have anything to do with formulae

I know, but that sheet is obviously to do with his finances/bank accounts and a lot of those items look like DDs or SOs and are thus date dependent. I figure he could use the date as a trigger to add to the total, instead of the colour of the cell content.