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

How about this...

=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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.