PDA

View Full Version : [SOLVED] Frequency of a number



leovilla64
06-30-2017, 10:51 AM
Frequency of a number

Hello, I would like to know how often one nr appears along with another.

Best regards
Leo

Leith Ross
06-30-2017, 11:02 AM
Hello Leo,

Put this formula in B3 and drag it down to B53...

=COUNTIF($C$3:$V$11,A3)

mdmackillop
06-30-2017, 11:40 AM
... and if you wish to show the "x" then use a custom number format of: 0"x"

leovilla64
06-30-2017, 12:07 PM
Thankful for the attention, but what I wanted to do is that when the number 3 that I used as an example (B1) appears in a line how many times it coincides with the other numbers. In the example I used the 7 that appears 2 times.

mdmackillop
06-30-2017, 01:25 PM
In B3 and copy down
=freq(A3,$B$1,$C$4:$V$11)
In standard module

Function Freq(x, y, data)
Dim Rw As Range, a As Long
For Each Rw In data.Rows
a = a + (Not Rw.Find(x, lookat:=xlWhole) Is Nothing) * (Not Rw.Find(y, lookat:=xlWhole) Is Nothing)
Next
If x = y Then
Freq = "#N/A"
Else
Freq = a
End If
End Function

leovilla64
07-03-2017, 05:30 AM
Grateful for the attention, that's what I needed.