Countif and offset problem over several columns

[jacko2401]

Hi

I am trying to record the number of times a someone gets a yellow or red card from a results worksheet for my local club. The results worksheet has column headings of the following:

Columns A-G (Match details), Column H (Player 1 name), Column I (Number of Goals scored), Column J (Yellow or Red), Column K (Player 2 name), Column I (Number of Goals scored), Column J (Yellow or Red) and so on for all 14 players.

In a seperate worksheet I am recording the stats with the following column headings:
Column A (Player Name), Column B (Appearances), Column C (Sub Appearances), Column D (Goals), Column E (Yellows), Column F (Reds)

I have got formulas for playter name, appearances, sub appearances and goals but unable to record the yellow or reds.

I am trying to construct a formula that searches for the player name throughout the results worksheet, takes an offset of 2 columns to the right (as that is the cell that will either be blank, Yellow or Red. It then needs to counts whether that cell's value is Yellow and count the occurences for that player. I then need to repeat this for Red.

I'm stumped as this formula has to do a 2 layer count ifs and is counting occurences of text being in a cell rather than counting numbers.

Does anyone have any idea how I can tackle this?

Thanks

 

[BiocideJ]

You can use a SUMPRODUCT with the second range offset by two columns. This example is a little more simplistic than yours but the concept is identical. If you don't understand it or need help implementing, just respond back.

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Match Details							p1 name	p1 goals	p1 cards	p2 name	p2 goals	p2 cards	p3 name	p3 goals	p3 cards				Yellow	Red
2								joe	1		sam	0		bob	0	Yellow			sam	0	0
3								sam	0		joe	0		bob	0				bob	1	1
4								bob	0	Red	joe	0	Red	sam	2				joe	0	1
Sheet1

Cell Formulas
Range	Formula
T2	=SUMPRODUCT(--($H$2:$N$4=$S2),--($J$2:$P$4=T$1))
T3	=SUMPRODUCT(--($H$2:$N$4=$S3),--($J$2:$P$4=T$1))
T4	=SUMPRODUCT(--($H$2:$N$4=$S4),--($J$2:$P$4=T$1))
U2	=SUMPRODUCT(--($H$2:$N$4=$S2),--($J$2:$P$4=U$1))
U3	=SUMPRODUCT(--($H$2:$N$4=$S3),--($J$2:$P$4=U$1))
U4	=SUMPRODUCT(--($H$2:$N$4=$S4),--($J$2:$P$4=U$1))