Monday, May 30, 2011

yogi_Compute Y The Number Of Occurrences Of X In A Specified Column

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
F.R.S. said:
It would be cool, if anybody can help me here. Unfortunately, I didn't find anything on the web so far :(
I have a table and in column D, I want to store multiple numbers as a comma separated list, e.g.:
D
03
04,10
10
05,07
10,12
etc.
In another sheet, I want to count how many cells contain a certain number. What I did so far is:
=COUNT(FILTER(Sheet1!D:D,SEARCH(10,Sheet1!D:D)))
If D is formated as numbers and only use one number (instead of a list) in every D-cell, this works fine.
On the other hand, the formula does not work anymore for comma separated list that is formated as text.
Interestingly, also single numbers don't work if they are formated as text.
------------------------------------------------------------------------
In the following solution I have computed Y for occurrence of X with different solutions in
Sheet1, Sheet2, and Sheet3