Hello,
this is my excel problem.
I have a table so structured (I add holiday column to explain my question,
but originally there is not in the table)

holiday day num
y 20/12 3
y 21/12 4
n 22/12 5
n 23/12 6
y 24/12 1
y 25/12 2
y 26/12 5
y 27/12 3
n 28/12 4

I have to extract, in another sheet, numbers according the day.
Main problem is that if I have a weekday after one or more consecutive
holidays
(I consider saturday and sunday like holidays), I have to sum the weekday
value
to the previous holiday(s) number(s).

So in the specified case, in another sheet, I have to find a situation like
this:

newdata total
22/12 12 (the sum of 22/12(w) + 21/12(h) + 20/12(h) values)
23/12 6 (only 23/12 value as weekday without previous
holidays)
28/12 15 (the sum of 28/12(w) + 27/12(h) + 26/12(h) +
25/12(h) + 24/12(h))


now, I set this kind of formula (where X and Z after "day", "newdata" and
"num" variables, are row and columns references):
IF(newdatax-(newdatax-1) = 5;
SUMPRODUCT((dayx:dayz=newdatax-4)*(dayx:dayz=newdatax-3)*(dayx:dayz=newdatax-2)*(dayx:dayz=newdatax-1)*(dayx:dayz=newdatax);(numx:numz);
IF(newdatax-(newdatax-1) = 4;
SUMPRODUCT((dayx:dayz=newdatax-3)*(dayx:dayz=newdatax-2)*(dayx:dayz=newdatax-1)*(dayx:dayz=newdatax);(numx:numz);............ )

and so on until arrive to the condition IF(newdata-(newdata-1) =
1;(dayx:dayz=newdatax);(numx:numz)

this kind of formula works, but is too long because in this way I have to
consider all possibility, starting from a maximum of 4 consecutive holidays
dates (that may occur under Christmas time, but it could happen).
My question is: is there a possibility to build up another formula more
optimized and short ?
I hope to explain well my trouble and I wait some suggestion about.
thanks.


Andrea.