calculating a sum based on txt color

[donjjjordan]

I have a budget sheet with listed expenses when i have made the purchase i turn the txt to red, i want to keep track of what has been spent.
ok hears an example

a1 = £10
a2 = £10
a3 = £25
a4 = £15
a6 = £35
a7 = total of red

i would like a formula to only calculate the red numbers please

[ben_hensel]

1) TRUE: You're manually changing the color to red? FALSE: Or have you changed the number format to color negative numbers red?

2) TRUE: Is the currency symbol part of the string, something you typed in, FALSE: or is it a number, formatted as currency, with the pound sign displayed but not actually part of the cell value?


If 1 is TRUE you're basically doing it wrong. I mean, that's not at all the way excel expects you to handle this, it's not how it was designed to handle things, so it's going to take some VBA to work around. CELL("color",reference) won't help us check.

IF 1 is FALSE, it's A7 = SUMIF(range, "<0", range)
You can reverse the sign on the operation by making A7 = -SUMIF(), if you want.


If 2 is TRUE, we're going to have to cleave off the text symbol and make it a number again before we do any math. That will take LEFT(RIGHT()) operations.

IF 2 is FALSE, no problem.

[donjjjordan]

1/ i manually change the color to red
2/ the cell is formatted to the £ currency symbol

i must add i have a very basic knowledge of excel

[Melvinrobb]

You could create a second column where you type Yes or No instead of manually changing the color. Then the following would work:

Cost Purchased
a1 = £10 b1 = No
a2 = £10 b2 = No
a3 = £25 b3 = Yes
a4 = £15 b4 = No
a6 = £35 b6 = Yes
a7 = =SUMIF(B1:B6,"Yes",A1:A6)

You can also create a conditional formatting on cells A1:a6 by using the following formula
=b1="yes"
Read up on conditional formatting if you are unsure of the steps.

[donjjjordan]

thank you so much you are a star