Hello

I can't seem to find if this can actually be done but, using Excel 2003, I
have a worksheet with several blocks containing cells numbered from 1 to 100.

From these blocks, I want to be able to count each cell as a value of 1 if I
change the background colour to red (or any other colour) then multiply that
value by a number, say 5.

eg If I select the first 25 cells and fill them red, I want my "total" Cell
to show the sum of 25 * 1 * 5 giving me 125.

Hope this is understandable!

Thanks for any help.

Look he

http://xldynamic.com/source/xld.ColourCounter.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"hollies" wrote in message ...
| Hello
|
| I can't seem to find if this can actually be done but, using Excel 2003, I
| have a worksheet with several blocks containing cells numbered from 1 to 100.
|
| From these blocks, I want to be able to count each cell as a value of 1 if I
| change the background colour to red (or any other colour) then multiply that
| value by a number, say 5.
|
| eg If I select the first 25 cells and fill them red, I want my "total" Cell
| to show the sum of 25 * 1 * 5 giving me 125.
|
| Hope this is understandable!
|
| Thanks for any help.
|
|

Thanks Niek for your very swift answer.

Unfortunately I am quite uncertain about formulae and although I have looked
at this page, I am still baffled.

I have put the formulae =SUMPRODUCT(--(ColorIndex(A1:A100)=3)) into a cell
but I just get the #NAME? error.

I presume I am doing something wrong but not sure what.

Sorry if I am being a little dim!

"Niek Otten" wrote:

Look he

http://xldynamic.com/source/xld.ColourCounter.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"hollies" wrote in message ...
| Hello
|
| I can't seem to find if this can actually be done but, using Excel 2003, I
| have a worksheet with several blocks containing cells numbered from 1 to 100.
|
| From these blocks, I want to be able to count each cell as a value of 1 if I
| change the background colour to red (or any other colour) then multiply that
| value by a number, say 5.
|
| eg If I select the first 25 cells and fill them red, I want my "total" Cell
| to show the sum of 25 * 1 * 5 giving me 125.
|
| Hope this is understandable!
|
| Thanks for any help.
|
|

You need to add the ColorIndex function to your workbook. See the code at
the bottom of the web page, under "Code Sample(s)".

Regards,
Fred.

"hollies" wrote in message
...
Thanks Niek for your very swift answer.

Unfortunately I am quite uncertain about formulae and although I have
looked
at this page, I am still baffled.

I have put the formulae =SUMPRODUCT(--(ColorIndex(A1:A100)=3)) into a cell
but I just get the #NAME? error.

I presume I am doing something wrong but not sure what.

Sorry if I am being a little dim!

"Niek Otten" wrote:

Look he

http://xldynamic.com/source/xld.ColourCounter.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"hollies" wrote in message
...
| Hello
|
| I can't seem to find if this can actually be done but, using Excel
2003, I
| have a worksheet with several blocks containing cells numbered from 1
to 100.
|
| From these blocks, I want to be able to count each cell as a value of 1
if I
| change the background colour to red (or any other colour) then multiply
that
| value by a number, say 5.
|
| eg If I select the first 25 cells and fill them red, I want my "total"
Cell
| to show the sum of 25 * 1 * 5 giving me 125.
|
| Hope this is understandable!
|
| Thanks for any help.
|
|

Many thanks Fred.

I knew there was something I was not doing but it is like learning a new
language. I am slow to start and it needs spelling out for me at the moment.

Tried it and it works a treat.

Thanks for your help.



"Fred Smith" wrote:

You need to add the ColorIndex function to your workbook. See the code at
the bottom of the web page, under "Code Sample(s)".

Regards,
Fred.

"hollies" wrote in message
...
Thanks Niek for your very swift answer.

Unfortunately I am quite uncertain about formulae and although I have
looked
at this page, I am still baffled.

I have put the formulae =SUMPRODUCT(--(ColorIndex(A1:A100)=3)) into a cell
but I just get the #NAME? error.

I presume I am doing something wrong but not sure what.

Sorry if I am being a little dim!

"Niek Otten" wrote:

Look he

http://xldynamic.com/source/xld.ColourCounter.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"hollies" wrote in message
...
| Hello
|
| I can't seem to find if this can actually be done but, using Excel
2003, I
| have a worksheet with several blocks containing cells numbered from 1
to 100.
|
| From these blocks, I want to be able to count each cell as a value of 1
if I
| change the background colour to red (or any other colour) then multiply
that
| value by a number, say 5.
|
| eg If I select the first 25 cells and fill them red, I want my "total"
Cell
| to show the sum of 25 * 1 * 5 giving me 125.
|
| Hope this is understandable!
|
| Thanks for any help.
|
|

Worked fine but.....

If I remove the red background or add more cells to the "red" list, the
calculations do not change, the total stay as it was.

eg =SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14 returns the value 140 if I
make the background RED for the first 10 cells.

If I then change the background of any of these cells by removing the RED or
if I add additional "RED" cells, the total value is not changing.

Again, am I missing something here.

Thanks

I have just found that by double clicking in the "Total" cell, the value
actually changes but is this correct.

Should I have to double click this cell after each/any change?

Thanks



"hollies" wrote:

Hello

I can't seem to find if this can actually be done but, using Excel 2003, I
have a worksheet with several blocks containing cells numbered from 1 to 100.

From these blocks, I want to be able to count each cell as a value of 1 if I
change the background colour to red (or any other colour) then multiply that
value by a number, say 5.

eg If I select the first 25 cells and fill them red, I want my "total" Cell
to show the sum of 25 * 1 * 5 giving me 125.

Hope this is understandable!

Thanks for any help.

Sounds like you're set to "manual" in ToolsOptionsCalculation.

Set to Automatic.


Gord Dibben MS Excel MVP

On Sun, 25 May 2008 16:44:00 -0700, hollies
wrote:

I have just found that by double clicking in the "Total" cell, the value
actually changes but is this correct.

Should I have to double click this cell after each/any change?

Thanks



"hollies" wrote:

Hello

I can't seem to find if this can actually be done but, using Excel 2003, I
have a worksheet with several blocks containing cells numbered from 1 to 100.

From these blocks, I want to be able to count each cell as a value of 1 if I
change the background colour to red (or any other colour) then multiply that
value by a number, say 5.

eg If I select the first 25 cells and fill them red, I want my "total" Cell
to show the sum of 25 * 1 * 5 giving me 125.

Hope this is understandable!

Thanks for any help.

No, Gord, I am set to Automatic.



"Gord Dibben" wrote:

Sounds like you're set to "manual" in ToolsOptionsCalculation.

Set to Automatic.


Gord Dibben MS Excel MVP

On Sun, 25 May 2008 16:44:00 -0700, hollies
wrote:

I have just found that by double clicking in the "Total" cell, the value
actually changes but is this correct.

Should I have to double click this cell after each/any change?

Thanks



"hollies" wrote:

Hello

I can't seem to find if this can actually be done but, using Excel 2003, I
have a worksheet with several blocks containing cells numbered from 1 to 100.

From these blocks, I want to be able to count each cell as a value of 1 if I
change the background colour to red (or any other colour) then multiply that
value by a number, say 5.

eg If I select the first 25 cells and fill them red, I want my "total" Cell
to show the sum of 25 * 1 * 5 giving me 125.

Hope this is understandable!

Thanks for any help.

Hi

Changing background itself doesn't force recalculation (at least in
Excel2000). Try this:
=IF(NOW()=NOW(),YourFormula)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"hollies" wrote in message
...
Worked fine but.....

If I remove the red background or add more cells to the "red" list, the
calculations do not change, the total stay as it was.

eg =SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14 returns the value 140 if I
make the background RED for the first 10 cells.

If I then change the background of any of these cells by removing the RED
or
if I add additional "RED" cells, the total value is not changing.

Again, am I missing something here.

Thanks

I have been "playing" with the formulae again this morning and cannot get it
to work automatically, either by adding RED or removing it.

=SUMPRODUCT(--(ColorIndex(B3:U7)=3))*15

The only way to get the calculation to work is by Double clicking in the
Total cell. Just clicking in the cell does not do the trick.

Could this be something to do with the colour?

Thanks, Rob


"hollies" wrote:

No, Gord, I am set to Automatic.



"Gord Dibben" wrote:

Sounds like you're set to "manual" in ToolsOptionsCalculation.

Set to Automatic.


Gord Dibben MS Excel MVP

On Sun, 25 May 2008 16:44:00 -0700, hollies
wrote:

I have just found that by double clicking in the "Total" cell, the value
actually changes but is this correct.

Should I have to double click this cell after each/any change?

Thanks



"hollies" wrote:

Hello

I can't seem to find if this can actually be done but, using Excel 2003, I
have a worksheet with several blocks containing cells numbered from 1 to 100.

From these blocks, I want to be able to count each cell as a value of 1 if I
change the background colour to red (or any other colour) then multiply that
value by a number, say 5.

eg If I select the first 25 cells and fill them red, I want my "total" Cell
to show the sum of 25 * 1 * 5 giving me 125.

Hope this is understandable!

Thanks for any help.

Thanks Arvi

However, I am sorry but I do not understand quite what you mean and what to
put as the formula!



"Arvi Laanemets" wrote:

Hi

Changing background itself doesn't force recalculation (at least in
Excel2000). Try this:
=IF(NOW()=NOW(),YourFormula)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"hollies" wrote in message
...
Worked fine but.....

If I remove the red background or add more cells to the "red" list, the
calculations do not change, the total stay as it was.

eg =SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14 returns the value 140 if I
make the background RED for the first 10 cells.

If I then change the background of any of these cells by removing the RED
or
if I add additional "RED" cells, the total value is not changing.

Again, am I missing something here.

Thanks

If YourFormula is =SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14
then Arvi is suggesting that you try
=IF(NOW()=NOW(),SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14)
--
David Biddulph

"hollies" wrote in message
...
Thanks Arvi

However, I am sorry but I do not understand quite what you mean and what
to
put as the formula!



"Arvi Laanemets" wrote:

Hi

Changing background itself doesn't force recalculation (at least in
Excel2000). Try this:
=IF(NOW()=NOW(),YourFormula)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"hollies" wrote in message
...
Worked fine but.....

If I remove the red background or add more cells to the "red" list, the
calculations do not change, the total stay as it was.

eg =SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14 returns the value 140 if
I
make the background RED for the first 10 cells.

If I then change the background of any of these cells by removing the
RED
or
if I add additional "RED" cells, the total value is not changing.

Again, am I missing something here.

Thanks

Thanks David

Unfortunately it has the same effect as the original formula.

It woll only make the calculation on double clicking in the3 Total cell!

Rob

"David Biddulph" wrote:

If YourFormula is =SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14
then Arvi is suggesting that you try
=IF(NOW()=NOW(),SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14)
--
David Biddulph

"hollies" wrote in message
...
Thanks Arvi

However, I am sorry but I do not understand quite what you mean and what
to
put as the formula!



"Arvi Laanemets" wrote:

Hi

Changing background itself doesn't force recalculation (at least in
Excel2000). Try this:
=IF(NOW()=NOW(),YourFormula)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"hollies" wrote in message
...
Worked fine but.....

If I remove the red background or add more cells to the "red" list, the
calculations do not change, the total stay as it was.

eg =SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14 returns the value 140 if
I
make the background RED for the first 10 cells.

If I then change the background of any of these cells by removing the
RED
or
if I add additional "RED" cells, the total value is not changing.

Again, am I missing something here.

Thanks

Hi

Then you have the calculation set to manual. Depending on your Excel
version, you can check it:
a) Excel 2000: ToolsOptionsCalculations - Calculation radio button must be
set to Automatic;
b) Excel 2007: Office ButtonExcel OptionsFormulas - Workbook Calculation
radio button must be set to Automatic.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"hollies" wrote in message
...
Thanks David

Unfortunately it has the same effect as the original formula.

It woll only make the calculation on double clicking in the3 Total cell!

Rob

"David Biddulph" wrote:

If YourFormula is =SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14
then Arvi is suggesting that you try
=IF(NOW()=NOW(),SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14)
--
David Biddulph

"hollies" wrote in message
...
Thanks Arvi

However, I am sorry but I do not understand quite what you mean and
what
to
put as the formula!



"Arvi Laanemets" wrote:

Hi

Changing background itself doesn't force recalculation (at least in
Excel2000). Try this:
=IF(NOW()=NOW(),YourFormula)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"hollies" wrote in message
...
Worked fine but.....

If I remove the red background or add more cells to the "red" list,
the
calculations do not change, the total stay as it was.

eg =SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14 returns the value 140
if
I
make the background RED for the first 10 cells.

If I then change the background of any of these cells by removing
the
RED
or
if I add additional "RED" cells, the total value is not changing.

Again, am I missing something here.

Thanks

An explanation how this IF(NOW()=NOW(),...) works.

Functions may be volatile or not volatile. Volatile is a function, which
starts formual calculation with any cell value changing (P.e. if you have
in some cell a formula =NOW(), then recalaculation is started every time you
edit any cell). Non-volatile starts recalculation only, when any of it's
parameters has changed (P.e. if you have in some cell a formula =A1, and
there are no other formulas, the recalculation is started only when you edit
cell A1, or the cell with formula).

I didn't check out the code for this ColorIndex function, but as making
functions volatile increases considerably the amount calculation time, I
assumed that ColorIndex() is non-volatile. The construction I adviced, makes
the formula volatile.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"hollies" wrote in message
...
Thanks David

Unfortunately it has the same effect as the original formula.

It woll only make the calculation on double clicking in the3 Total cell!

Rob

"David Biddulph" wrote:

If YourFormula is =SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14
then Arvi is suggesting that you try
=IF(NOW()=NOW(),SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14)
--
David Biddulph

"hollies" wrote in message
...
Thanks Arvi

However, I am sorry but I do not understand quite what you mean and
what
to
put as the formula!



"Arvi Laanemets" wrote:

Hi

Changing background itself doesn't force recalculation (at least in
Excel2000). Try this:
=IF(NOW()=NOW(),YourFormula)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"hollies" wrote in message
...
Worked fine but.....

If I remove the red background or add more cells to the "red" list,
the
calculations do not change, the total stay as it was.

eg =SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14 returns the value 140
if
I
make the background RED for the first 10 cells.

If I then change the background of any of these cells by removing
the
RED
or
if I add additional "RED" cells, the total value is not changing.

Again, am I missing something here.

Thanks

Thanks Arvi

I have checked this and it is already set to Automatic!

Rob

"Arvi Laanemets" wrote:

Hi

Then you have the calculation set to manual. Depending on your Excel
version, you can check it:
a) Excel 2000: ToolsOptionsCalculations - Calculation radio button must be
set to Automatic;
b) Excel 2007: Office ButtonExcel OptionsFormulas - Workbook Calculation
radio button must be set to Automatic.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"hollies" wrote in message
...
Thanks David

Unfortunately it has the same effect as the original formula.

It woll only make the calculation on double clicking in the3 Total cell!

Rob

"David Biddulph" wrote:

If YourFormula is =SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14
then Arvi is suggesting that you try
=IF(NOW()=NOW(),SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14)
--
David Biddulph

"hollies" wrote in message
...
Thanks Arvi

However, I am sorry but I do not understand quite what you mean and
what
to
put as the formula!



"Arvi Laanemets" wrote:

Hi

Changing background itself doesn't force recalculation (at least in
Excel2000). Try this:
=IF(NOW()=NOW(),YourFormula)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"hollies" wrote in message
...
Worked fine but.....

If I remove the red background or add more cells to the "red" list,
the
calculations do not change, the total stay as it was.

eg =SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14 returns the value 140
if
I
make the background RED for the first 10 cells.

If I then change the background of any of these cells by removing
the
RED
or
if I add additional "RED" cells, the total value is not changing.

Again, am I missing something here.

Thanks