software

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 quot;sum by colorquot; 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.

www.cpearson.com/excel/colors.htmGord Dibben Excel MVP

On Tue, 20 Dec 2005 16:38:02 -0800, quot;Barbaraquot;
gt; wrote:

gt;We have a sales spreadsheet that shows confirmed and projected sales. The
gt;projected income from various items is shown in red in our spreadsheet, while
gt;confirmed sales are in black.
gt;
gt;My question is, can we create a formula at the bottom that automatically
gt;totals only the red coloured figures, leaving out the black, without having
gt;to click on each individual cell?

Thanks Gord,

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

Thanks

Barbara

quot;Gord Dibbenquot; wrote:

gt; Barbara
gt;
gt; Excel does not have a built-in quot;sum by colorquot; function.
gt;
gt; You are forced to use a User Defined Function for this step.
gt;
gt; See Chip Pearson's site for more on this and code for downloading.
gt;
gt; www.cpearson.com/excel/colors.htm
gt;
gt;
gt; Gord Dibben Excel MVP
gt;
gt; On Tue, 20 Dec 2005 16:38:02 -0800, quot;Barbaraquot;
gt; gt; wrote:
gt;
gt; gt;We have a sales spreadsheet that shows confirmed and projected sales. The
gt; gt;projected income from various items is shown in red in our spreadsheet, while
gt; gt;confirmed sales are in black.
gt; gt;
gt; gt;My question is, can we create a formula at the bottom that automatically
gt; gt;totals only the red coloured figures, leaving out the black, without having
gt; gt;to click on each individual cell?
gt;

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!
quot;Barbaraquot; wrote:

gt; Thanks Gord,
gt;
gt; Is there perhaps an easier way - maybe tagging the cells or something?
gt;
gt; Thanks
gt;
gt; Barbara
gt;
gt; quot;Gord Dibbenquot; wrote:
gt;
gt; gt; Barbara
gt; gt;
gt; gt; Excel does not have a built-in quot;sum by colorquot; function.
gt; gt;
gt; gt; You are forced to use a User Defined Function for this step.
gt; gt;
gt; gt; See Chip Pearson's site for more on this and code for downloading.
gt; gt;
gt; gt; www.cpearson.com/excel/colors.htm
gt; gt;
gt; gt;
gt; gt; Gord Dibben Excel MVP
gt; gt;
gt; gt; On Tue, 20 Dec 2005 16:38:02 -0800, quot;Barbaraquot;
gt; gt; gt; wrote:
gt; gt;
gt; gt; gt;We have a sales spreadsheet that shows confirmed and projected sales. The
gt; gt; gt;projected income from various items is shown in red in our spreadsheet, while
gt; gt; gt;confirmed sales are in black.
gt; gt; gt;
gt; gt; gt;My question is, can we create a formula at the bottom that automatically
gt; gt; gt;totals only the red coloured figures, leaving out the black, without having
gt; gt; gt;to click on each individual cell?
gt; gt;

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.GordOn Tue, 20 Dec 2005 20:45:02 -0800, quot;Barbaraquot;
gt; wrote:

gt;Thanks Gord,
gt;
gt;Is there perhaps an easier way - maybe tagging the cells or something?
gt;
gt;Thanks
gt;
gt;Barbara
gt;
gt;quot;Gord Dibbenquot; wrote:
gt;
gt;gt; Barbara
gt;gt;
gt;gt; Excel does not have a built-in quot;sum by colorquot; function.
gt;gt;
gt;gt; You are forced to use a User Defined Function for this step.
gt;gt;
gt;gt; See Chip Pearson's site for more on this and code for downloading.
gt;gt;
gt;gt; www.cpearson.com/excel/colors.htm
gt;gt;
gt;gt;
gt;gt; Gord Dibben Excel MVP
gt;gt;
gt;gt; On Tue, 20 Dec 2005 16:38:02 -0800, quot;Barbaraquot;
gt;gt; gt; wrote:
gt;gt;
gt;gt; gt;We have a sales spreadsheet that shows confirmed and projected sales. The
gt;gt; gt;projected income from various items is shown in red in our spreadsheet, while
gt;gt; gt;confirmed sales are in black.
gt;gt; gt;
gt;gt; gt;My question is, can we create a formula at the bottom that automatically
gt;gt; gt;totals only the red coloured figures, leaving out the black, without having
gt;gt; gt;to click on each individual cell?
gt;gt;