Sumproduct with blanks or rows with numbers

[FryGirl]

How can I modify this formula to not return value due to the cells with the number? Rows 8 and row 9 are causing the error.

Book1
	A	B	C	D
1	Header
2	131 All (xx) AT H		131 All (xx) AT H	#VALUE!
3	131 All (xx) HH D
4	131 All (xx) AT H
5	131 All (xx) DD
6	131 All (xx) AT H
7	131 All (xx) LM
8	1
9	1
10	159 All (jj)
11	159 All (jj)
12	159 All (jj)
13	159 All (jj)
14	159 All (jj)
15	159 All (jj)
16	159 All (jj)
17	159 All (jj)
18	131 All (xx) LM
Sheet1 (4)

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(--(LEFT(A2:A18,FIND(")",A2:A18))=LEFT(C2,FIND(")",C2))))

 

[FryGirl]

Thanks Fluff. I can work with the Countifs, but the Sumproduct was still returning just a 1

 

[Fluff]

They both return 7 for me

Fluff.xlsm
	A	B	C	D
1	Header
2	131 All (xx) AT H		131 All (xx) AT H	7
3	131 All (xx) HH D			7
4	131 All (xx) AT H
5	131 All (xx) DD
6	131 All (xx) AT H
7	131 All (xx) LM
8	1
9	1
10	159 All (jj)
11	159 All (jj)
12	159 All (jj)
13	159 All (jj)
14	159 All (jj)
15	159 All (jj)
16	159 All (jj)
17	159 All (jj)
18	131 All (xx) LM
19
Update

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(--(IFERROR(LEFT(A2:A18,FIND(")",A2:A18)),0)=LEFT(C2,FIND(")",C2))))
D3	D3	=COUNTIFS(A2:A18,LEFT(C2,FIND(")",C2))&"*")

 

[FryGirl]

Not sure I understand why, but it still only returns 1

Book1
	A	B	C	D
1	Header
2	131 All (xx) AT H		131 All (xx) AT H	1
3	131 All (xx) HH D			7
4	131 All (xx) AT H
5	131 All (xx) DD
6	131 All (xx) AT H
7	131 All (xx) LM
8	1
9	1
10	159 All (jj)
11	159 All (jj)
12	159 All (jj)
13	159 All (jj)
14	159 All (jj)
15	159 All (jj)
16	159 All (jj)
17	159 All (jj)
18	131 All (xx) LM
Sheet2

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(--(IFERROR(LEFT(A2:A18,FIND(")",A2:A18)),0)=LEFT(C2,FIND(")",C2))))
D3	D3	=COUNTIFS(A2:A18,LEFT(C2,FIND(")",C2))&"*")

 

[Fluff]

Not sure why it doesn't work for you, unless it's something to do with your version of xl.