Coun cells with colour and text help please

[Lensmeister]

I have a sheet called "data" and in it there is a range of cells named "players". Some of the cells have a background colour of red (due to being sent off).

On Sheet called "Players" I want a column that counts the number of times a players name has a red background in the data sheet.

For example:

Sheet "Data"
Range "players"


Sheet "Players"
Cell "Z1" has the player name "Jones.J"
in Cell "AA1" I want it to count the number of times "Jones.J" appears WITH a "red" background in the datasheet "data" in the range "players".

Is this possible?
if yes .. How?
Please.

[Bob Phillips]

See http://www.xldynamic.com/source/xld.....html#counting

[Lensmeister]

Thanks.

I get that using

=SUMPRODUCT(--(ColorIndex(A1:A100)=3))

Will return all the cells with a red backgroud, but I additionally I need it to count those with Jones.J AND a red background.

How can I do this?

[Bob Phillips]

Try

=SUMPRODUCT(--(ColorIndex(A1:A100)=3),--(A1:A100="Jones J."))

[Lensmeister]

I have made the full ranges of cells that can have the red background named SendOffs (not original I know) and the players name is in cell P871

Therefore in excel 2002 the formula should be
=SUMPRODUCT(--(ColorIndex(SendOffs)=3),--(SendOffs=P871))

Is this correct?

If so why is it showing #NAME? ?

[ron2k_1]

This may sound a little silly, but do you have the code for the UDF installed already on your workbook?

You need to put the code provided on a separate module.

[Lensmeister]

This may sound a little silly, but do you have the code for the UDF installed already on your workbook?

You need to put the code provided on a separate module.

Nope ... I might be dumb here but what UDF?

[ron2k_1]

An UDF stands for User Defined Function. It is in effect a function created by VBA code because Mr Gates didn't give it to us by default on Excel. The code for the function is at the bottom of the of the page that B.P. gave to you. Copy everything and paste in VBE:

1. Highlight Code and copy,
2. Go back to excel sheet
3. Press Alt + F11
4. Go to insert>Module
5. Paste code,
6. Go back to excel wb and use the function

If you don't what colour index you used on the range, determine colour index by using the formula as stated on the first paragraph of the page.

[Lensmeister]

I have copied the VBA code in and it still showed #NAME? si I deleted the formula and re-entered it.

but still showing #NUM! when it finishes recalculating.

[ron2k_1]

Works for me. Select different player from C1, and you'll see that it changes. Ensure that you enable macros, and that your macro security is set to medium or lower.

[Lensmeister]

Your one did with me too..
I need to rethink how to do this maybe.

I am trying to calculate how many times a player has been sent off. Thing is I have over 3000 results and the teams are written as each game is a row. Hence a selection ofthe range od team being SendOffs being all the possible team line up.

The Players are on another sheet and that calculated the players appearances with countif, but I also want to add the sending offs in to a column.

I make the players sent off with a Red background and white text.

I didn't want to totally rewrite the whole workbook from scratch

I'd love to be able to get this amount of data into access and do it in that, but I like the countif function to calculate things. It's a monster spreadsheet of about 25MB

Any suggestions?

[ron2k_1]

The background color of the cell is the only indication you have that the player has been sent off? If you have some other indicator on another column like an "SO" or a date, or anything on another column, then you can probably use an IF combination and get a count pretty easily.

Can you give us little mock up, cut your data set to as small as possible and tell us what your objectives are. And I'm pretty sure we can give a tip or two.

[Lensmeister]

I have decided to do it a little differently.

I have a small bit of code that allows me to crate a filter, then I can input the details on to another sheet manually. A set of countif's then do the calcs I need.

Thanks for your help everyone