EXCEL

[f.gotelli104]

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

[#2]

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

[#3]

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