# 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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### Jim North

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

#### nehpets12

##### Active Member
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

##### New Member
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
7
Views
180
Replies
10
Views
288
Replies
2
Views
85
Replies
8
Views
127
Replies
4
Views
334

1,147,748
Messages
5,742,976
Members
423,769
Latest member
LongToast

### 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.

### Which adblocker are you using?

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

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