PDA

View Full Version : Find number of lines ?

alikirca20
04-12-2009, 12:05 AM
I have some number groups. I have also 5 numbers. I want to find numbers of lines in groups, include these 5 numbers together? Can I do it only by using one cell and formule? I explained my question in detail by added file. Thanks.

lucas
04-12-2009, 07:02 AM
I can count the number of rows the value in R1 is found using an array formula:

=SUM(IF(FREQUENCY(IF(A2:P19=(R1),ROW(A2:P19)),IF(A2:P19=(R1),ROW(A2:P19)))>0,1,0))

but I can't figure out how to include the other four numbers??

Zack Barresse
04-12-2009, 08:04 AM

=COUNTIF(A2:P2,{1,2,3,4,5})

lucas
04-12-2009, 08:35 AM
Zack, it looks good initially but if you add a 1 to any row it will incriment instead of requiring all of the numbers....

georgiboy
04-12-2009, 11:16 AM
Perhaps you could try a code option like this...

Sub CountNum()
Dim rCell As Range, nCell As Range, pCell As Range
Dim Row As Range
Dim X As Long, Y As Long

X = 0
Y = 0

For Each rCell In Range("X3:X19").Cells
rCell.Value = ""
For Each Row In Range("A2:P19").Rows
For Each pCell In Range(rCell.Offset(, -6), rCell.Offset(, -2)).Cells
If WorksheetFunction.CountIf(Row, pCell.Value) > 0 Then
X = X + 1
If X = 5 Then
Y = Y + 1
rCell.Value = Y
X = 0
End If
End If
Next pCell
X = 0
Next Row
Y = 0

Next rCell

End Sub
Hope this helps

Zack Barresse
04-12-2009, 12:48 PM
Gotcha Steve. I misunderstood the intent. I'd still stick with a formulatic approach vs. VBA though.

If you wanted to use a helper column you could use a standard FREQUENCY function and then SUM that data range...
=--(FREQUENCY(A2:P2,R2:V2)>0)

HTH