=SUMPRODUCT revisited

  • Thread starter Thread starter hollies
  • Start date Start date
H

hollies

Hello

I have a problem with my worksheets and would appreciate some help. I
received help on this when I set it up 5 months ago but now cannot get it to
work.

My page is split into varying blocks of cells and I want to count the number
of sales by changing the cell background to red.

This gives me a graphic picture of my sales as well as a numerical total
which is then used in further formulae.

For example, if 32 items are sold I would block out 32 cells (from a group
of 100) in RED and I want to display the total of 32 in the total sales
cell.

So....

Total Sales Cell V7 has the formula =SUMPRODUCT(--(ColorIndex(B3:U7)=3)

Total Sales Cell V11 has the formula
=SUMPRODUCT(--(ColorIndex(B9:U11)=3))

Total Sales Cell V15 has the formula
=SUMPRODUCT(--(ColorIndex(B13:U15)=3))

etc...etc....

As it did work on first attempt I presume the above is the way to go but
then, as it is not now working, is there a basic error here or a better way
to get the same result.

Many thanks

Rob
 
Thanks Bob

Now sorted it and it works when pressing F9.

One other question involving the same cells and formula. How can I get a
number in a cell to change to Bold and Black when the cell background is
changed to RED. Can find this in conditional formatting.

Cheers, Rob
 
Thanks again Bob

However, not sure I have this right.

I have selected a block of cells and entered CF and made condition 1
formula Is =ColorIndex(A1)=3 , clicked the Format button and made the font
style bold. I cannot change the size or type of font as these are greyed
out.

When I then try to apply the format, Excel crashes!!

Regards, Rob
 
CF does not allow changing of the font size or style, just bold, italic and
so on. But as to Excel crashing, I have no idea. I did try it before posting
just to confirm, and it worked fine here. What Excel do you have?
 
Just a complete guess...

Try changing your printer (or at least printer driver) to something else. Maybe
it's the interaction between excel and the printer driver that's causing the
trouble.
 
Ps. There have been lots of posts that describe problems with network
printers--although these problems are speed related (changing to bold takes a
very long time for the initial change).

Maybe using a local printer would solve your problem.

(again a guess...)
 
Back
Top