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

                                          Google Spreadsheet   Post  #1251
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 18, 2013
user MeBjammin (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Y9XaohyCAfw)
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

adding iferror:

=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)))

at least returns a blank (or whatever else I want)

however, all three of these codes (primarily the second and even more so the third) seem a bit superfluous especially when trying to edit them. there has to be a better way.

here is a mock up:

https://docs.google.com/spreadsheet/ccc?key=0AjIduJt8dPWqdHVFU3FVcnBTSUZFOFhwclJQSlJyNUE#gid=0

it contains time stamps in A2:A with their respective scores in B2:B. A representation of each formula style is there as well as a manually counted and a version pulling from a second tab (which is how it will have to work in the actual doc).

Any ideas would be great, thank you!

--------------------------------------------------------------------------------------------------------------------------------------