Formulae for summing specific cells

[Zongear]

I have a sheet for recording football data each week - every player's team colour, goals and assists are recorded.

On separate sheets, I am trying to create stats for each player. I would like to count the number of goals each player has scored for each colour team - e.g. Sum the number of goals 'Matt' scored while playing for the 'Red' team.

I have used the MOD function previously the sum just the numbers in the 'Goals' column for each player, but now don't know how to get it to 'check' the colour each week before summing.

Any help greatly appreciated.

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

This is smaller & on a single sheet but would this structure help?

24 03 25.xlsm
	A	B	C	D	E	F	G	H	I	J
2		Team	Goals	Assists	Team	Goals	Assists	Team	Goals	Assists
3	Matt	Red		1	Red	2	1	Red
4	Johnny	Red	5	1	Red		1	Red
5	Carl	Red		1	Red	3		Blue	2
6
7
8	Goals
9		Red	Blue
10	Matt	2	0
11	Johnny	5	0
12	Carl	3	2
Count Goals

Cell Formulas
Range	Formula
B10:C12	B10	=SUMPRODUCT(IFERROR(($A$3:$A$5=$A10)*($B$3:$H$5=B$9)*$C$3:$I$5,0))