Count content of cell based on background colour 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.

 

[T. Valko]

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.

I recommend NOT doing it this way.

It's complicated, requires VBA code and it WON'T work the way you think it should work.

If you use a column and put an "X" in it to note when a player is "sent off" then it's a simple matter to count the number of Xs for a player.

 

[T. Valko]

I recommend NOT doing it this way.

It's complicated, requires VBA code and it WON'T work the way you think it should work.

If you use a column and put an "X" in it to note when a player is "sent off" then it's a simple matter to count the number of Xs for a player.

Something like this...

Book1
	A	B	C	D	E
1	Player	Sent Off	_	Player	Sent Off
2	Player 5	X	_	Player 5	2
3	Player 1	_	_	_	_
4	Player 5	X	_	_	_
5	Player 1	X	_	_	_
6	Player 2	X	_	_	_
7	Player 5	_	_	_	_
8	Player 2	_	_	_	_
9	Player 2	X	_	_	_
10	Player 5	_	_	_	_
11	Player 2	X	_	_	_
12	Player 4	_	_	_	_
13	Player 1	_	_	_	_
14	Player 3	X	_	_	_
15	Player 3	_	_	_	_
Sheet1

One of these formulas entered in E2:

If you're using Excel 2007 or later:

=COUNTIFS(A2:A15,D2,B2:B15,"X")

This one will work in ANY version of Excel:

=SUMPRODUCT(--(A2:A15=D2),--(B2:B15="X"))

 

[Kenneth Hobson]

When using methods that count interior colors, press F9 to force a calculation to update your UDF values. See: http://www.mrexcel.com/forum/showthread.php?t=566637

 

[T. Valko]

When using methods that count interior colors, press F9 to force a calculation to update your UDF values. See: http://www.mrexcel.com/forum/showthread.php?t=566637

Will you remember to do a forced calculation every time you change the format?

This is why I suggest not doing any calculations based on cell formats.

 

[Kenneth Hobson]

There are advantages and disadvantages for each method. I leave it to the user to decide what works best for them. I believe in letting the user decide. Sometimes they like my idea and sometimes not. More solutions means more choices. Hopefully, it is not a Hobson's choice...

 

[T. Valko]

There are advantages and disadvantages for each method. I leave it to the user to decide what works best for them. I believe in letting the user decide. Sometimes they like my idea and sometimes not. More solutions means more choices. Hopefully, it is not a Hobson's choice...

Yeah, that's a good position to have but the OP should be made aware of potential unexpected results.

 

[Lensmeister]

Thanks This is a real help and I am starting to understand what I am doing (well sort of).

The only problem with this is the team line up is held in a row. i.e.

P1, p2, p3 etc.

P1 P2 P3 P4 P5 P6
Dix.J Mallaby.D Curnow.P Campbell.1 Young.P Shears.K
Dix.J Mallaby.D Leng.T Campbell.1 Young.P Shears.K
Hamlet.W Mallaby.D Leng.T Johnson Young.P Bell.W
Hamlet.W Mallaby.D Leng.T Bell. Young.P Johnson.
Hamlet.W Mallaby.D Rogers Hutchinson.F Argyle.N Johnson.
Hamlet.W Mallaby.D Rogers Leng.T Argyle.N Johnson.
Hamlet.W Mallaby.D Rogers Leng.T Argyle.N Bell.W
Hamlet.W Mallaby.D Rogers Rayson Argyle.N Johnson.


eek!!! That never lined up right, but you get my drift.

Now you see what having the "X" at the end would not be prudent. I do already have a formula that shows if there is a red background in a row, but that is so I can do an auto filer to populate a separate sheet.

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

 

[T. Valko]

Thanks This is a real help and I am starting to understand what I am doing (well sort of).

The only problem with this is the team line up is held in a row. i.e.

P1, p2, p3 etc.

P1 P2 P3 P4 P5 P6
Dix.J Mallaby.D Curnow.P Campbell.1 Young.P Shears.K
Dix.J Mallaby.D Leng.T Campbell.1 Young.P Shears.K
Hamlet.W Mallaby.D Leng.T Johnson Young.P Bell.W
Hamlet.W Mallaby.D Leng.T Bell. Young.P Johnson.
Hamlet.W Mallaby.D Rogers Hutchinson.F Argyle.N Johnson.
Hamlet.W Mallaby.D Rogers Leng.T Argyle.N Johnson.
Hamlet.W Mallaby.D Rogers Leng.T Argyle.N Bell.W
Hamlet.W Mallaby.D Rogers Rayson Argyle.N Johnson.


eek!!! That never lined up right, but you get my drift.

Now you see what having the "X" at the end would not be prudent. I do already have a formula that shows if there is a red background in a row, but that is so I can do an auto filer to populate a separate sheet.

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

The #NAME? error means Excel doesn't recognize one of the names used in the formula.

Chances are it doesn't recognize the name ColorIndex.

Since I don't advocate this method I'll let Kenneth Hobson take it from here.

Good luck!

 

[Lensmeister]

Thanks Biff.

All help is good help

I have had the sheet for years and it functions practically automatically so a change would cause a complete revamp. And I don't have that much hair left on my head for that ... lol