# CountIf

#### nehpets12

##### Active Member
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
Book5
ABCDEF
1010007
2
3P025292/2PLASMAPART08.0MM
4P025293/2PLASMAPART08.0MM
5P010007/2PLASMAPART08.0MM1COUNTIF(12:12,A5)12MATCH(C1,A1:A17,0)
6P013489/2PLASMAPART08.0MM
7P013715/7PLASMAPART08.0MM
8P013781/2PLASMAPART08.0MM
9
10
11
12010007P010007/1P010007/2011032
13010229P010229/1R1200
14010245R1056
15010249P010249
16010275P010275011011
17010297P010297
18
Sheet1

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
I'm not clear what you are doing. What column are you trying to sum?

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

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

Replies
2
Views
106
Replies
3
Views
357
Replies
9
Views
261
Replies
1
Views
80
Replies
3
Views
82

1,218,496
Messages
6,142,817
Members
450,449
Latest member
Dave Carr QM

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back