One route...
First create a key, concatenating each Volt recording, eg:
G2: =":"&B2&"::"&C2&"::"&D2&"::"&E2&"::"&F2&"::"&":"
copied down to G15
Then to allow for relatively consistent formulae...
L2:L5 enter 2 to 5 respectively
repeat for L7:L10 & L12:L15
Into M2:M5 enter 24
Into M7:M10 enter 48
With the above in place we can proceed to summarise...
I2: =COUNTIF($G$2:$G$15,"*:"&SUBSTITUTE(TRIM(REPT(" "&$M2&" ",$L2))," ",":*:")&":*")
copied to I5
I2:I5 copied to I7:I10
then
J12: =SUMPRODUCT(--(((LEN($G$2:$G$15)-LEN(SUBSTITUTE(SUBSTITUTE($G$2:$G$15,24,""),48,"")))/2)>=$L12))
copied down to J15
then
J17: =SUMPRODUCT(--(LEN($G$2:$G$15)<>LEN(SUBSTITUTE(SUBSTITUTE($G$2:$G$15,24,""),48,""))))
J18: =COUNTIF($G$2:$G$15,REPT(":",12))
J19: =COUNT($B$2:$F$15)
or
J19: =SUMPRODUCT(LEN($G$2:$G$15)-LEN(SUBSTITUTE(SUBSTITUTE($G$2:$G$15,24,""),48,"")))/2
(obviously first option is the better!)
Attached is above in working form...
Bookmarks