Count Consecutive Grades if in Core Courses and Consecutive

[anthonyexcel]

Good morning,

I need to count how many students have concecutive values {"F","D","D+","D-"} if the subject is ("ELA","Science","SS","Math"). I have a formula that I am using the problem is that if a row has one of the values in it but they are not consecutuve it will count it. Any help would be appreciated. Thanks!

=SUMPRODUCT(--($A$2:$A$12=H2)*($B$2:$B$12={"ELA","Math","SS","Science"})*(MMULT(--ISNUMBER(MATCH($C$2:$F$12,{"D","D-","D+","F"},0)),{1;1;1;1})>1))

ID Subject Q1 Q2 Q3 Q4 2222111 ELA A+ B D D 2222111 1 2222111 Math A D C C 33334444 2 2222111 SS C D C C 2222111 Science D A A- A+ 2222111 PE D F F F 33334444 Language C B B B 33334444 SS D C B D- 33334444 PE D B D C- 33334444 Art C D D D 33334444 Math B A D D+ 33334444 Science C- D F C

 

[StephenCrump]

What exactly do you mean by consecutive?

And for the screenshot illustrated, what result(s) do you expect to see?

 

[anthonyexcel]

Thanks, StephenCrump,

Consecutive in this case is a {"D","D-","D+","F"} in (Q1 and Q2)
or (Q2 and Q3) or (Q3 and Q4)

Results are:
2222111 =1
33334444 = 2

I have some VBA Code that I use to find if a row has consecutive in them. But was trying to get a formula count.

Public Function ConsecutiveCount(ByVal r As Range, ByVal s As String) As Long
Dim cell As Range
Dim i As Long
Dim j As Long
    For Each cell In r.Cells
    If (s = "ELA" Or s = "SS" Or s = "Math" Or s = "Science") Then
        If (cell.Value = "D" Or cell.Value = "F" Or cell.Value = "D-" Or cell.Value = "D+") Then
            i = i + 1
            j = Application.max(j, i)
        Else
            i = 0
        End If
    End If
    Next cell
    If j > 1 Then
        ConsecutiveCount = 1
    End If
End Function

ID Subject Q1 Q2 Q3 Q4 2222111 ELA A+ B D D 1 2222111 Math A D C C 0 2222111 SS C D C C 0 2222111 Science D A A- A+ 0 2222111 PE D F F F 0 33334444 Language C B B B 0 33334444 SS D C B D- 0 33334444 PE D B D C- 0 33334444 Art C D D D 0 33334444 Math B A D D+ 1 33334444 Science C- D F C 1

=ConsecutiveCount(C2:F2,B2)

 

[Eric W]

There's probably a better way, but try:

Book1
	A	B	C	D	E	F	G	H	I
1	ID	Subject	Q1	Q2	Q3	Q4
2	2222111	ELA	A+	B	D	D
3	2222111	Math	A	D	C	C		2222111	1
4	2222111	SS	C	D	C	C		33334444	2
5	2222111	Science	D	A	A-	A+
6	2222111	PE	D	F	F	F
7	33334444	Language	C	B	B	B
8	33334444	SS	D	C	B	D-
9	33334444	PE	D	B	D	C-
10	33334444	Art	C	D	D	D
11	33334444	Math	B	A	D	D+
12	33334444	Science	C-	D	F	C
Sheet3

Cell Formulas
Range	Formula
I3:I4	I3	=ROWS(FILTER(A$2:A$12,(A$2:A$12=H3)*ISNUMBER(MATCH(B$2:B$12,{"ELA","Math","SS","Science"},0))*(ISNUMBER(MATCH(C$2:C$12,{"D","D-","D+","F"},0))*ISNUMBER(MATCH(D$2:D$12,{"D","D-","D+","F"},0))+ISNUMBER(MATCH(D$2:D$12,{"D","D-","D+","F"},0))*ISNUMBER(MATCH(E$2:E$12,{"D","D-","D+","F"},0))+ISNUMBER(MATCH(E$2:E$12,{"D","D-","D+","F"},0))*ISNUMBER(MATCH(F$2:F$12,{"D","D-","D+","F"},0)))))

 

[anthonyexcel]

Eric W, better way or not! That seems to do the trick! Thank you so much for the help I have been stuck on this so a few days. Appreciate your help!

 

[Eric W]

Happy to help!

And just FYI, here's a shorter way, if it's "better" is debatable!

Book1
	A	B	C	D	E	F	G	H	I
1	ID	Subject	Q1	Q2	Q3	Q4
2	2222111	ELA	A+	B	D	D
3	2222111	Math	A	D	C	C		2222111	1
4	2222111	SS	C	D	C	C		33334444	2
5	2222111	Science	D	A	A-	A+
6	2222111	PE	D	F	F	F
7	33334444	Language	C	B	B	B
8	33334444	SS	D	C	B	D-
9	33334444	PE	D	B	D	C-
10	33334444	Art	C	D	D	D
11	33334444	Math	B	A	D	D+
12	33334444	Science	C-	D	F	C
Sheet3

Cell Formulas
Range	Formula
I3:I4	I3	=IFERROR(ROWS(FILTER(A$2:A$12,(A$2:A$12=H3)*ISNUMBER(MATCH(B$2:B$12,{"ELA","Math","SS","Science"},0))*ISNUMBER(MATCH(MMULT(--ISNUMBER(MATCH(C$2:F$12,{"D","D-","D+","F"},0)),{1;2;4;8}),{3,6,7,12,13,14,15},0)))),0)

 

[anthonyexcel]

Nice!! Thank you again!!!!