Am Wed, 31 Jul 2013 01:28:37 +0000 schrieb Robert AH Prins:

for 1 try:

=SUMPRODUCT((MATCH(A1:A10&D1

10,A1:A10&D1

10,0)=ROW(1:10)*(A1:A10=1)*(D1

10<>""))*1)

Care to explain how this actually works given that it does not even reference Column B?

FWIW, I've temporarily added a column to the sheet with the following formula, and assuming that the dates are in cols C & F, and put the formula in column "W"

=IF(C1=C2,0,1)+IF(AND(C1<>F1,F1<>C2),F1-C1,IF(AND(C1<>F1,F1=C2),F1-C1-1,0))

and sum that (for the appropriate discriminant with a "=SUMIF(Data!A1:A2998,Calcs!A1,Data!W1:W2998)"), the totals will be OK.

Now the next question is, is this re-creatable without using a new column?

As for my formula:

The first "IF" statement: if the dates on two consecutive rows in the first column are the same, we do not want to count the first of those two dates.

The second "IF" statement: if the two dates in a single row are the same, AND the second date is not equal to the first date in the next row, then the number of unique dates is difference between the two same-row dates, unless and that is

The third "IF" statement, the first date on the next row is equal to the second date on the first row, in which case we need the above difference minus 1.

This works out for all 124 sections of the "data" sheet, maybe it can be coded shorter, but this way I will still understand it in a years time.