Hey everyone,,
I asked on the general forum earlier how to do this only refering to one range and got the equation:
=COUNT(1/FREQUENCY(IF('Shipping and Invoice'!$J$2:$J$23548=B2+0,IF('Shipping and Invoice'!$L$2:$L$23548<>"",MATCH('Shipping and Invoice'!$L$2:$L$23548,'Shipping and Invoice'!$L$2:$L$23548,0))),ROW('Shipping and Invoice'!$L$2:$L$23548)-ROW('Shipping and Invoice'!$L$2)+1))
Now what this equation does is looks at column J which is a date range (B2 is the date that the user wants the data for). Based on that date range is only looks for the corresponding range of rows in Column L which are order numbers. Now there are multiple entires ofr a single order. So what the equation does is only count each order once. So there is a list of 5 5 5 4 4 6 6 3, the return count is 4 (once for each value), again only focus on the cells that corresponding to the date entered in B2
Well I need this same thing but also refering to another range. So IDENTICAL to what is above but adding in a time range so instead of just looking at the subtotal count for 4/25/11 I need to look at the subtotal count for 4/25/11 before 11 PM (And actually I need this for 4 distinct ranges that are the smae everything which are before 11, between 11 and 1, between 1 and 3:30 and after 3:30
I know the equaiton for just the data takes forever to calculate with the huge range (the range has to tbe the large overall) so I am guessing a macro makes more sense for this. Any advice?!!
Thanks!
Bookmarks