Frequency on VBA in a different array arranging

[montecarlo2012]

Hello everyone.
I understand the Frequency function require an array like multiplication model,
but, in my case at the moment I have different needs
Please check this out

MR EXCEL.xls
	B	C	D	E	F
1
2	14	18	25	27	29
3	15	18	22	24	34
4	4	9	34	35	36
5	5	6	15	19	33
6	13	21	28	31	32
7	2	3	22	23	25
8	1	3	10	20	30
9	1	8	15	22	27
Sheet1

in this case what I really need is the frequency of 36 cells and not the frequency of the array 7 * 5
so my question is
how to do that on vba
in my minisheet the regular use of frequency will be (B2:F8)
but, is not what I need, would be B2:F8 plus B8

in other words how to choose any range but not necessarily a perfect array.
thank you for reading this.

I upload the images because the xx2 do not show the formatting that illustrate the idea

so I need the frequency of the cells with the borders only.

 

[montecarlo2012]

=FREQUENCY(B2:F8*B9,P2:P37)

in this way accept the extra cell, but do not count anything,
somebody maybe can tell me about
SUMIF, or COUNTIF, ||"any formula "|| that work in cases where a perfect array is not part of the problem
thanks

 

[montecarlo2012]

at least this formula take the cells I want,

but the output is all = "0" so help need it

 

[montecarlo2012]

Hi, the rate of views/answers make me think, that maybe excel itself is not build to answer this kind of questions, in other words everything has to be linear or square or rectangular, other wise
is not possible to answer.
Plus the people read 2 answers and is not, is a comments from myself, so if somebody want to help base on that info, then I don't get help.
thank you for reading this.

 

[bebo021999]

=FREQUENCY(B2:F8*B9,P2:P37)

in this way accept the extra cell, but do not count anything,
somebody maybe can tell me about
SUMIF, or COUNTIF, ||"any formula "|| that work in cases where a perfect array is not part of the problem
thanks

What is in P2:P37? If they are blank, Frequency will be 0
may be:
=FREQUENCY(B2:F8*B9,ROW(P1:P36))
with ROW(P1:P36) = {1,2,3,...,36}
But I believe the range size still be 7*2=35, not 36

 

[bebo021999]

And here is UDF solution

Book2
	B	C	D	E	F	G									P
2	14	18	25	27	29										1
3	15	18	22	24	34										2
4	4	9	34	35	36										3
5	5	6	15	19	33										4
6	13	21	28	31	32										5
7	2	3	22	23	25										6
8	1	3	10	20	30										7
9	1	8	15	22	27										8
10															9
11															10
12	2		=freq(P2:P37,B2:F8,B9)												11
13															12
14															13
15															14
16															15
17															16
18															17
19															18
20															19
21															20
22															21
23															22
24															23
25															24
26															25
27															26
28															27
29															28
30															29
31															30
32															31
33															32
34															33
35															34
36															35
37															36
Sheet1

Cell Formulas
Range	Formula
B12	B12	=freq(P2:P37,B2:F8,B9)
P3:P37	P3	=P2+1

Option Explicit
Function freq(binRange As Range, dataRange1 As Range, Optional dataRange2 As Range) As Variant
    Dim i As Long, ii As Long, j As Long
    Dim freq1(), dataV1, dataV2, binV
    On Error Resume Next
    ' Convert data range and bin range to arrays
    dataV1 = dataRange1.Value
    dataV2 = dataRange2.Value
    binV = binRange.Value
    
    ' Initialize the frequency array
    ReDim freq1(1 To UBound(binV) - 1, 1 To 1)
    
    ' Loop through each data value 1 and count frequencies
    For i = 1 To UBound(dataV1)
        For ii = 1 To UBound(dataV1, 2)
            If IsNumeric(dataV1(i, ii)) Then
                For j = 1 To UBound(binV) - 1
                    If dataV1(i, ii) >= binV(j, 1) And dataV1(i, ii) < binV(j + 1, 1) Then
                        freq1(j, 1) = freq1(j, 1) + 1
                        Exit For
                    End If
                Next
            End If
        Next
    Next
    
    ' Loop through each data value 2 and count frequencies
    For i = 1 To UBound(dataV2)
        For ii = 1 To UBound(dataV2, 2)
            If IsNumeric(dataV2(i, ii)) Then
                For j = 1 To UBound(binV) - 1
                    If dataV2(i, ii) >= binV(j, 1) And dataV2(i, ii) < binV(j + 1, 1) Then
                        freq1(j, 1) = freq1(j, 1) + 1
                        Exit For
                    End If
                Next
            End If
        Next
    Next
    ' Return the frequency array
    freq = freq1
End Function

 

[montecarlo2012]

Thank you bebo. great job

 

[montecarlo2012]

What is in P2:P37? If they are blank, Frequency will be 0
may be:
on P2:P37 is the array of numbers from 1:36

 

[montecarlo2012]

And here is UDF solution

meaning one out of many?
you don't need to answer this really
is just an intellectual matter
UDF because is shorter.

Thank you bebo you are great