Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Excel Formula Question

Aug 7, 2004 4:30AM PDT

I wonder if anyone knows of a way to sub-total a column of numbers based on the background color of the cell.

I keep a basic Excel 2003 worksheet for the family budget. Column titles for the months, row titles for the bills. When a bill amount is paid, I color the background of that cell yellow so I can tell at a glance what's been paid and what hasn't for a particular month.

For instance, right now there are several numbers in the August column with yellow backgrounds and the rest of the amounts still have no background color.

Is there a way to keep a running total of the items left to be paid each month? In other words, can you add a column of numbers and exclude the ones with a background color?

What I've been doing so far is using a simple formula of "=cell+cell+cell+cell+cell...", but you have to go into the formula and delete cells manually as items are paid. I'm looking for an easier way . . .

Hope this hasn't been too wordy. Thanks!

Discussion is locked

- Collapse -
Re: Excel Formula Question
Aug 7, 2004 5:48AM PDT
- Collapse -
Re: Excel Formula Question
Aug 7, 2004 7:16AM PDT

Thanks for the suggestion, ****. I posted over there and we'll see what happens.

- Collapse -
Re: Excel Formula Question
Aug 7, 2004 7:19AM PDT

While this is NOT an answer to your exact request, the way you worded it made me wonder if you were aware of a simple ability of Excel.

Your wording made me think that you go through the laborous task of deleting cells from a formula and typing in the new cells. Much too tedious.

Here is a simple Excel thing that you can do to enter a new formula, that instead of entering in the cell locations just select them.

Here is what I mean:
Say you have a formula like:
A4 =B1+C2+D3+E4
and you want it updated to now be
A4 =B5+C6+D7+E8

Just select the cell A4, (you'll see the old formula)
and press the "=" key (that deletes the old formula)
then, use the mouse to select the first new cell, B5
press the "+" key
use the mouse to select the next new cell, C6
press the "+" key
use the mouse to select the next new cell, D7
press the "+" key
use the mouse to select the next new cell, E8
press the "Enter" key

That's all. That way you don't have to type in any cell location. You just have to add in the operator (the "+") between cell selections.

I have seen so many people type in cell locations, not realizing that Excel can enter it for you. And since you alreay have the cells you want in a non-colored state, it should be a very easy screen selection.

I have another tip, for doing what you might want to do, being more automated, but if your data is relatively easy, then this selection method works pretty good.

- Collapse -
Re: and an automated way.
Aug 7, 2004 7:32AM PDT

I am not going to go through the steps of actually doing this, but I will give you a hint of how it can be done.

Instead of going through the steps of coloring the cells of items you now want excluded, just make an interspersed set of columns (or rows, whichever works best for your layout) to hold a "special" character. That character might be a simple "p" for paid, or "x" for whatever, or anything else you can easily type. That interspersed column is only so that you can easily type that character to mark a payment paid.

Next you set your other columns to be conditionally formatted with a background color when the adjacent cell has that "p" in it.

That way your changing of a cells color status is as simple as typing a "p" in that adjacent cell.

Finally, you want to set up a formula to add up all the cells that do not have the adjacent "p" next to them.

So, your formula would add up cells not yet paid.
Conversely, you could also make another formula, in another cell, to add up all the paid items(the ones with the adjacent "p"), if that information might be nice to know as well.

Good luck.

- Collapse -
Re: Excel Formula Question
Aug 7, 2004 3:30PM PDT

Chuck T,

Thanks for the reminder. I actually do know that I can click a cells for inclusion in a formula and that typing their location is not needed. That's how I usually string cells together in formulas.

Your second post (about conditional formatting) is what I will work on tomorrow. It sounds like a good approach to my problem.

I really appreciate your time!

- Collapse -
Re: Excel Formula Question
Aug 7, 2004 9:46PM PDT

You could either assign a function to do this for you or you could make a button in your worksheet, then assign this procedure to the button to make it more elegant. Here is the code:

Function AddRedCells(r as Range)
For Each r In r.Cells
If IsNumeric(r) Then
If r.Font.ColorIndex = 3 Then
AddRedCells = AddRedCells + r.Value
End If
End If
Next r
End Function

- Collapse -
Re: Excel Formula Question
Aug 9, 2004 4:40AM PDT

I improved my code with this one. You could make a button on your excel worksheet and assign this code:

Sub Button1_Click()
Dim r As Range
Dim total As Currency
For Each r In ActiveSheet.Range("A1:A10")
If IsNumeric(r) Then
If r.Interior.ColorIndex = 3 Then
total = total + r.Value
End If
End If
Next r
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = total
End Sub