CountIf

nehpets12

Active Member
Joined
Feb 22, 2002
Messages
453
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
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

nehpets12

Active Member
Joined
Feb 22, 2002
Messages
453
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
Joined
Aug 20, 2002
Messages
46
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
 

Forum statistics

Threads
1,143,739
Messages
5,720,578
Members
422,292
Latest member
Bernd0501

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?

Disable AdBlock

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
Top