We have a sales spreadsheet that shows confirmed and projected sales. The
projected income from various items is shown in red in our spreadsheet, while
confirmed sales are in black.

My question is, can we create a formula at the bottom that automatically
totals only the red coloured figures, leaving out the black, without having
to click on each individual cell?

Barbara

Excel does not have a built-in "sum by color" function.

You are forced to use a User Defined Function for this step.

See Chip Pearson's site for more on this and code for downloading.

http://www.cpearson.com/excel/colors.htm


Gord Dibben Excel MVP

On Tue, 20 Dec 2005 16:38:02 -0800, "Barbara"
wrote:

We have a sales spreadsheet that shows confirmed and projected sales. The
projected income from various items is shown in red in our spreadsheet, while
confirmed sales are in black.

My question is, can we create a formula at the bottom that automatically
totals only the red coloured figures, leaving out the black, without having
to click on each individual cell?

Thanks Gord,

Is there perhaps an easier way - maybe tagging the cells or something?

Thanks

Barbara

"Gord Dibben" wrote:

Barbara

Excel does not have a built-in "sum by color" function.

You are forced to use a User Defined Function for this step.

See Chip Pearson's site for more on this and code for downloading.

http://www.cpearson.com/excel/colors.htm


Gord Dibben Excel MVP

On Tue, 20 Dec 2005 16:38:02 -0800, "Barbara"
wrote:

We have a sales spreadsheet that shows confirmed and projected sales. The
projected income from various items is shown in red in our spreadsheet, while
confirmed sales are in black.

My question is, can we create a formula at the bottom that automatically
totals only the red coloured figures, leaving out the black, without having
to click on each individual cell?

how are the cells coulured in the first place???Are they manually
fomrmatted?If you have some kind of conditional format use those condotions
to do a sumif()
--
paul
remove nospam for email addy!



"Barbara" wrote:

Thanks Gord,

Is there perhaps an easier way - maybe tagging the cells or something?

Thanks

Barbara

"Gord Dibben" wrote:

Barbara

Excel does not have a built-in "sum by color" function.

You are forced to use a User Defined Function for this step.

See Chip Pearson's site for more on this and code for downloading.

http://www.cpearson.com/excel/colors.htm


Gord Dibben Excel MVP

On Tue, 20 Dec 2005 16:38:02 -0800, "Barbara"
wrote:

We have a sales spreadsheet that shows confirmed and projected sales. The
projected income from various items is shown in red in our spreadsheet, while
confirmed sales are in black.

My question is, can we create a formula at the bottom that automatically
totals only the red coloured figures, leaving out the black, without having
to click on each individual cell?

Barbara

If the cells are formatted via Conditional Formatting use the same criteria
for summing.

If manually formatted, any tagging would have to be done through some sort of
value using a COUNTIF formaula or somesuch, not by the formatting.

If there is an easier way, I hope to learn it.


Gord


On Tue, 20 Dec 2005 20:45:02 -0800, "Barbara"
wrote:

Thanks Gord,

Is there perhaps an easier way - maybe tagging the cells or something?

Thanks

Barbara

"Gord Dibben" wrote:

Barbara

Excel does not have a built-in "sum by color" function.

You are forced to use a User Defined Function for this step.

See Chip Pearson's site for more on this and code for downloading.

http://www.cpearson.com/excel/colors.htm


Gord Dibben Excel MVP

On Tue, 20 Dec 2005 16:38:02 -0800, "Barbara"
wrote:

We have a sales spreadsheet that shows confirmed and projected sales. The
projected income from various items is shown in red in our spreadsheet, while
confirmed sales are in black.

My question is, can we create a formula at the bottom that automatically
totals only the red coloured figures, leaving out the black, without having
to click on each individual cell?