Formulas cannot look to a cells color. What you can do is have a separate
column with (maybe) the phrase "PAID" in it next to each paid bill. You can
then use a SUMIF formula to get a total of all paid bills.
For ex:
=SUMIF(G5:G100,"PAID",D5
100)
Where:
G5:G100 = the range containing possible word PAID
"PAID" = the phrase in G5:G100 that denotes paid bills
D5
100 = range containing the bill amounts
Similarly, you can get a total of unpaid bills using the following SUMIF
formula
=SUMIF(G5:G100,"<>PAID",D5
100)
Where the <> means not equal to
Finally, you can use the Format-->Conditional Formatting command to
automatcially apply your green colors when each bill is paid:
1. Select all of the cells you want to turn green when the bills are paid
In my example, the bills start in row 5
2. Click Format-->Conditional formatting
3. Change the first dropdown (on the left) to "Formula Is"
4. Enter the following formula in the textbox to the right of "Formula Is":
=$G5="PAID"
Where:
$G5 = the cell on the first row of your bills that may/may not
contain the word "PAID"
"PAID" = the word in that cell that will make the font color change.
Make
sure you place the word in quotes.
5. Click the Format button and on the Font Tab change the font color as
desired
6. Click OK twice
