Conditionally calculate formula in spreadsheet.
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? |
Conditionally calculate formula in spreadsheet.
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? |
Conditionally calculate formula in spreadsheet.
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? |
Conditionally calculate formula in spreadsheet.
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? |
Conditionally calculate formula in spreadsheet.
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? |
All times are GMT +1. The time now is 02:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com