ERROR IN FORMULA - COUNTIF/SUMPRODUCT, etc...???

[OLineCoach]

Hopefully we can solve this issue. On my worksheet to track football player's stats I am in a pickle. I have two separate sheets - one for OFFENSE, and one for STATS. The STATS sheet generates output using data from the OFFENSE data.

I am tracking each play of a football game. The player who touches the ball (either as a runner or receiver) has his number entered in Col. E

If the play is a run, the number of yards (pos or neg) go in Col. F

If the play is a completed pass, the number of yards go in Col. H

Example below of OFFENSE:

STATJACK_SOUTH FLORENCE_TooFixErrors.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3							1=Keep						1 = YES	1=YES
4				Ball			2=Lose			1=Yes	Pen		RUN	PASS
5	Q	DOWN	DIST	On	PLAYER	RUN	FUM?	PASS	QB	INT?	Yds	1st D?	TD	TD
6	1	1	10	34	2	8
7	1	2	2	42	2	11						1
8	1	1	10	47	24	6
9	1	2	4	41	22	10						1
10	1	1	10	31	2	2
11	1	2	8	29	7	1
12	1	3	7	28	1			28	4					1
OFFENSE

PROBLEM:
On the STATS sheet, the number of player rushing attempts (col. C) is generating a count of RUNS and RECEPTIONS combined. Am currently using a COUNTIF formula, which is wrong.

I want to be able to separate the number of rush attempts from the number of plays the player had a reception. Sample of STATS sheet is below as well.

STATJACK_SOUTH FLORENCE_TooFixErrors.xls
	A	B	C	D	E
4		RUSHING
5	Player Name	Number	ATT	YARDS	TD
6	OFF BY 11 ATT	2	10	41	0
7	Too Many	4	13	12	0
8	Counting Pass plays	5	1	68	1
9	too	7	5	12	0
10		22	4	32	0
11		24	8	22	0
INDIVID STATS

THANKS FOR ANY AND ALL HELP!

 

[fairwinds]

Hi,

Try:

=SUMPRODUCT(--(OFFENSE!$E$6:$E$168=B6),--(OFFENSE!$F$6:$F$168<>""))

and

=SUMPRODUCT(--(OFFENSE!$E$6:$E$168=B6),--(OFFENSE!$H$6:$H$168<>""))