Forum Discussion

f.gotelli104's avatar
f.gotelli104
Copper Contributor
Oct 28, 2018

EXCEL

Hi staff,

I had a problem with some formulas on Excel.
How can I calculate the frequency of a number in a specific cell in various sheets of a same table?
(for example the frequency of number 2 in cell B2 from sheet 1 to sheet 133)

Please help me, it's for my degree!

Regards,

Francesca

P.S. sorry but my English is not my mother tongue

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    Hi Francesca,

     

    In order to calculate the frequency (number of occurrences) of that number, you have to create a list in one of these sheets or in a separate sheet, and then bring all the numbers from all sheets to this list using a formula so that all of them you have in one place, in one column.

     

    let's say that the sheets are named this way:

    Sheet1, Sheet2, ... Sheet133

     

    If so, please use the below formula in a separate sheet, and drag it down until you return them all.

    =TRIM(INDIRECT("Sheet"&ROW(B1)&"!B2"))

     

    After that, use this formula to get the frequency:

    =SUMPRODUCT(LEN(B1:B3))-SUMPRODUCT(LEN(SUBSTITUTE(B1:B3,"2","")))

     

     

    Please find an example of this in the attached file.

     

    Hope that helps

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    try this:

    Sub CountOccurrenceOf2inB2()
    Dim I As Integer
    mcnt = 0
    For I = 1 To 133
    If Worksheets(I).Range("B2") = 2 Then
    mcnt = mcnt + 1
    End If
    Next I
    MsgBox "Occurrence for 2 in Cell B2 = " & mcnt
    End Sub

    HTH

Resources