What formula to use?

[hollies]

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.

 

[Niek Otten]

Look here:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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.
|
|

 

[hollies]

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!

 

[Fred Smith]

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]

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.

 

[hollies]

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

 

[hollies]

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

 

[Gord Dibben]

Sounds like you're set to "manual" in Tools>Options>Calculation.

Set to Automatic.


Gord Dibben MS Excel MVP

 

[hollies]

No, Gord, I am set to Automatic.

Sounds like you're set to "manual" in Tools>Options>Calculation.

Set to Automatic.


Gord Dibben MS Excel MVP

 

[Arvi Laanemets]

Hi

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

 

[hollies]

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]

Thanks Arvi

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

 

[David Biddulph]

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)

 

[hollies]

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

 

[Arvi Laanemets]

Hi

Then you have the calculation set to manual. Depending on your Excel
version, you can check it:
a) Excel 2000: Tools>Options>Calculations - Calculation radio button must be
set to Automatic;
b) Excel 2007: Office Button>Excel Options>Formulas - Workbook Calculation
radio button must be set to Automatic.

 

[Arvi Laanemets]

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.

 

[hollies]

Thanks Arvi

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

Rob