CountIf

[nehpets12]

I am trying to get a formula to look up a range

I am using the formula SUMIF the first part is to select the range. I am trying to use the formula MATCH to get the range. I can find the row number ie 12 but not sure how to put in a range of 12:12 into the SUMIF Formula Please Help

If I use the ADDRESS FORMULA I can get it to set the range as $A$12
ADDRESS(MATCH(C1,A1:A17,0),1)

Book5
	A	B	C	D	E	F
1			010007
2
3	P025292/2	PLASMAPART08.0MM
4	P025293/2	PLASMAPART08.0MM
5	P010007/2	PLASMAPART08.0MM	1	COUNTIF(12:12,A5)	12	MATCH(C1,A1:A17,0)
6	P013489/2	PLASMAPART08.0MM
7	P013715/7	PLASMAPART08.0MM
8	P013781/2	PLASMAPART08.0MM
9
10
11
12	010007		P010007/1	P010007/2	011032
13	010229		P010229/1	R1200
14	010245		R1056
15	010249		P010249
16	010275		P010275	011011
17	010297		P010297
18
Sheet1

 

[Jim North]

I'm not clear what you are doing. What column are you trying to sum?

 

[nehpets12]

I am trying to find if P010007/2 is in the row that contains 010007

I am looking up C1 "010007" in a worksheet that contains lots of parts in column A. This finds the row number that it is in. I then need to find if A5 "P010007/2" is entered in this row and how many times.

Hope this is a little clearer

 

[fidgen]

I'm using the following to scan down a worksheet in column A, matching the data there to Column A of a second worksheet. If the data matches, then is scans along that row looking for "1" Then when it finds "1" it enters an if formula and away we go!

Maybe you could adapt this so that when it matches the rows, it counts the things you're looking for in that row?

Have a look, i've edited it down to bare bones for you.


cheers
Hugh





=============================================
Sub match()

X = 2
For A = 2 To 5000 ' a catchall for any data you enter in the future
If Worksheets("sheet2").Cells(A, 1) = "" Then
Exit For
End If

Value2 = Worksheets("sheet2").Cells(A, 1) 'sheet bringing values into
For B = X To 5000
Value1 = Worksheets("sheet1").Cells(B, 1) 'sheet looking up values from

If Value1 = Value2 Then
For C = 2 To 50 ' the number of columns in the row you want to scan
If Worksheets("sheet2").Cells(A, C) = 1 Then

' Then put something you want done in here!
' Count, or make cells = other cells etc

Exit For
End If

Next 'C
Exit For
End If

Next 'B
X = B
Next 'A

End Sub