## Tuesday, June 18, 2013

### yogi_Count Instances Of Numbers In Column B By Week For Dates in Column A

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 18, 2013
countunique returns a 1
Hello All,
I was wondering if maybe you might have a better idea of a work around as what I came up with seems to me like it should not work.
I have an array of data where column A contains a time stamp (unique times, no repeats), and column B contains either nothing (a formula that yields a blank) or any integer from 0 to 5.
What I want is a way to see how many occurrences (time stamps) I have all total and how many occurrences of each number (don't need to count blanks, just how many zeros or ones or twos, so on) all within a time frame.

I used this code for the date:
``` =countunique(filter(\$A\$2:\$A, \$A\$2:\$A>=\$D6, \$A\$2:\$A<\$D5, \$A\$2:\$A<>"")) ```
and this one for the number (replacing the 0 at the end with 1, 2, 3, 4 or 5):
``` =countif(filter(\$B\$2:\$B, \$A\$2:\$A>=\$D6, \$A\$2:\$A<\$D5, \$B\$2:\$B<>""), 0) ```
which works, however, if there were NO time stamps for a given time frame (D5 to D6) then instead of returning 0 or nothing, it returned a 1 for the date, and reported 1 zero. Which doesn't work as we have dead weeks where there are no time stamps recorded but we need to see the over all patterns from week to week over a month or so.

I then used this:
``` =if(filter(\$A\$2:\$A, \$A\$2:\$A>=\$D14, \$A\$2:\$A<\$D13, \$A\$2:\$A<>"")="","",countunique(filter(\$A\$2:\$A, \$A\$2:\$A>=\$D14, \$A\$2:\$A<\$D13, \$A\$2:\$A<>""))) ```
``` =if(filter(\$B\$2:\$B, \$A\$2:\$A>=\$D14, \$A\$2:\$A<\$D13, \$B\$2:\$B<>"")="","",countif(filter(\$B\$2:\$B, \$A\$2:\$A>=\$D14, \$A\$2:\$A<\$D13, \$B\$2:\$B<>""), 0)) ```
which instead of returning a 1 returns a #N/A with error: No valid data

``` =iferror(if(filter(\$A\$2:\$A, \$A\$2:\$A>=\$D22, \$A\$2:\$A<\$D21, \$A\$2:\$A<>"")="","",countunique(filter(\$A\$2:\$A, \$A\$2:\$A>=\$D22, \$A\$2:\$A<\$D21, \$A\$2:\$A<>"")))) ```
``` =iferror(if(filter(\$B\$2:\$B, \$A\$2:\$A>=\$D22, \$A\$2:\$A<\$D21, \$B\$2:\$B<>"")="","",countif(filter(\$B\$2:\$B, \$A\$2:\$A>=\$D22, \$A\$2:\$A<\$D21, \$B\$2:\$B<>""), 0))) ```