CountIFS Issue

[SFCChase]

I have a larger version of the data sheet below. Basically I need to calculate the number of initial exam failures. The trouble I'm having is when a student passes their initial exam but it was a retest version (for other calculation reasons I can't put this "initial" score in the initial column since it's a different test version). I was using a COUNTA formula and just counting the number of retests taken, but it still counts an initial pass (of a retest version) as a test failure. Passing score is a 76. If a student fails the initial and passes a retest they will get a grade of 76 (no matter what the retest score was)

INITIAL 1	RETEST 1A	RETEST 1B	FINAL 1	INITIAL 2	RETEST 2A	RETEST 2B	FINAL 2	INITIAL 3	RETEST 3A	RETEST 3B	FINAL 3	TEST FAILURES
80			80	80			80	80			80	0
74	80		76	80			80	80			80	1
74	72	80	76	80			80	80			80	1
80			80		80		80	80			80	0
80			80	74	80		76		80		80	1
	80		80		80		80		80		80	0
74	80		76	74	80		76	74	80		76	3

I have tried using COUNTIF formulas but can't find one that works. I'm sure there's a simple set of IF equations or something out there, but i can't seem to come up with it today.

Thanks in advance

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	INITIAL 1	RETEST 1A	RETEST 1B	FINAL 1	INITIAL 2	RETEST 2A	RETEST 2B	FINAL 2	INITIAL 3	RETEST 3A	RETEST 3B	FINAL 3	TEST FAILURES
2	80			80	80			80	80			80	0
3	74	80		76	80			80	80			80	1
4	74	72	80	76	80			80	80			80	1
5	80			80		80		80	80			80	0
6	80			80	74	80		76		80		80	1
7		80		80		80		80		80		80	0
8	74	80		76	74	80		76	74	80		76	3
Sheet1

Cell Formulas
Range	Formula
L2:L8,H2:H8,D2:D8	L2	=IF(IF(I2="",J2,I2)>=76,IF(I2="",J2,I2),IF(MAX(J2:K2)>=76,76,""))
M2:M8	M2	=SUMPRODUCT(--(LEFT(A$1:I$1,7)="INITIAL"),--(A2:I2<>""),--(A2:I2<76))+SUMPRODUCT(--(LEFT(A$1:I$1,7)="INITIAL"),--(A2:I2=""),--(B2:J2<76))

 

[SFCChase]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	INITIAL 1	RETEST 1A	RETEST 1B	FINAL 1	INITIAL 2	RETEST 2A	RETEST 2B	FINAL 2	INITIAL 3	RETEST 3A	RETEST 3B	FINAL 3	TEST FAILURES
2	80			80	80			80	80			80	0
3	74	80		76	80			80	80			80	1
4	74	72	80	76	80			80	80			80	1
5	80			80		80		80	80			80	0
6	80			80	74	80		76		80		80	1
7		80		80		80		80		80		80	0
8	74	80		76	74	80		76	74	80		76	3
Sheet1

Cell Formulas
Range	Formula
L2:L8,H2:H8,D2:D8	L2	=IF(IF(I2="",J2,I2)>=76,IF(I2="",J2,I2),IF(MAX(J2:K2)>=76,76,""))
M2:M8	M2	=SUMPRODUCT(--(LEFT(A$1:I$1,7)="INITIAL"),--(A2:I2<>""),--(A2:I2<76))+SUMPRODUCT(--(LEFT(A$1:I$1,7)="INITIAL"),--(A2:I2=""),--(B2:J2<76))

I was able to get the L2 formula to work, but when I swapped in the M2 formula it isn't calculating. I did make 1 small change. My Initial label cells are actually much longer so instead of using LEFT I used RIGHT since all the cells would end with Initial. Is that the source of my error?

 

[SFCChase]

Unfortunately I'm on a Govt computer and can't download the Excel Book tool thing that would let you see my actual workbook. I'll try to be as clear as I can. I have a workbook that has the following setup (Columns B through F) for a total of 26 exams.

Name	Basic Nursing Initial	Basic Nursing Retest A	Basic Nursing Retest B	Calculation	Basic Nursing Final	Test Failures
				IF(A2="","",IF(B2>=76,B2,IF(C2>=76, 76,IF(D2>=76,76,B2)	IF(E2=0,"",E2)	was using =COUNTA(C2)
Jones	80			80	80	0
Smith	74	80		76	76	1
Andrews		80		80	80	0

I used the calculation column because I can hide it and have blank workbook for exams that students haven't taken (the final column) without a bunch of #VALUE errors. The following standards for class need to be tracked/met: If a student passes the initial exam they get that score (will be used later for GPA calculations). If they fail an initial exam they will take a retest (or 2 if necessary)....passing either of those exams will give them a 76 (for GPA purposes) and allow them to move on to the next exam. If they are unsuccessful on all 3 attempts, their initial failing score is used (for GPA calculation later). I can't label first attempts as the "initial" because there are lots of statistics and breakdown work done on the test versions. If a student misses the initial exam they are allowed to take the retest and that will be considered their initial try (but must be tracked in the retest version column for test statistical analysis). If they pass, they will get that score but my current COUNTA formula ends up tracking it as a failure when it shouldn't be. I have a total of 26 exams per class and an average class size of 70 so I'm hoping that i can just write out formulas on the top line and apply them all the way down.

In the example above I would need the 80 for Andrews to be calculated as an 80 for his GPA and no test failure.

I can't re-label any of the column headers because I use those specific names in INDEX formulas in other tabs that help me transcribe grades from the data flow sheet to a formal grade sheet.

I hope this is a little clearer of what i need. I apologize for the lenghty post.

 

[bebo021999]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Basic Nursing Initial	Basic Nursing Retest A	Basic Nursing Retest B	Basic Nursing Final	Basic Nursing Initial	Basic Nursing Retest A	Basic Nursing Retest B	Basic Nursing Final	Basic Nursing Initial	Basic Nursing Retest A	Basic Nursing Retest B	Basic Nursing Final	Test Final
2	Jones	80			80	80			80	80			80	0
3	Smith	74	80		76		80		80				0	2
4	Andrews	74	72	80	76	74	72	80	76	74	72	80	76	3
5		72	72	74	0	74	72	80	76	74	72	80	76	3
Sheet1

Cell Formulas
Range	Formula
I2:I5,E2:E5,M2:M5	M2	=(SUM(J2:L2)>0)*CHOOSE(COUNTIF(J2:L2,"<80")+1,80,76,76,0)
N2:N5	N2	=(E2<80)+(I2<80)+(M2<80)