=SUMPRODUCT revisited

[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

 

[Bob Phillips]

Yes, there is no ColorIndex function builtin to Excel, you have to add your
own. If you got the idea from
http://xldynamic.com/source/xld.ColourCounter.html, use the UDF provided
there.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[hollies]

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

 

[Bob Phillips]

Yes, you use the Colorindex function in the CF

=ColorIndex(A1)=3

 

[hollies]

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

 

[Bob Phillips]

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?

 

[Dave Peterson]

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.

 

[Dave Peterson]

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