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))))

 

[Legacy 143009]

=IF(NOT(ISNUMBER($A2)),SUMPRODUCT(--(LEFT(A2:A18,FIND(")",A2:A18))=LEFT(C2,FIND(")",C2)))))

 

[Z51]

Does throwing in an error checker work? (I assume the expected answer is 7 in your example?)

=SUMPRODUCT(--(IFERROR(LEFT(A2:A18,FIND(")",A2:A18)),"")=LEFT(C2,FIND(")",C2))))

 

[FryGirl]

Neither one of these works for me. With the first one I get #VALUE! and the second answer returns 1

 

[Legacy 143009]

=IFERROR(SUMPRODUCT(--(LEFT(A2:A18,FIND(")",A2:A18))=LEFT(C2,FIND(")",C2)))),"")

 

[FryGirl]

This doesn't seem to be working either. Wrapping the Sumproduct in an IFERROR does not seem to be evaluating the #VALUE! error that the non-text (or blank) cells or throwing within the formula.

How do I correct for this

=SUMPRODUCT(--({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;#VALUE!;#VALUE!;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}))

 

[AhoyNC]

Try:

Book2
	A	B	C	D
1	Header
2	131 All (xx) AT H		131 All (xx) AT H	7
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
Sheet2

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

 

[kevin9999]

Is it just a simple count you're after?

clean.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
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
Sheet3

Cell Formulas
Range	Formula
D2	D2	=COUNTIF(A2:A18,LEFT(C2,9)&"*")

 

[FryGirl]

Yes Kevin, that would for sure work if the left was always going to be 9. Unfortunately, that won't always be the case, that is why I need to find the first close parenthesis.

AhoyNC, that just returns a 1

 

[Fluff]

How about

=SUMPRODUCT(--(IFERROR(LEFT(A2:A18,FIND(")",A2:A18)),0)=LEFT(C2,FIND(")",C2))))

or

=COUNTIFS(A2:A18,LEFT(C2,FIND(")",C2))&"*")