Forum Discussion
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...
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