Ok....

I'm having a hard time trying to figure out your setup.

This formula will work but we may need to tweak it until I fully understand
your setup:

=SUMPRODUCT(--(C13:Z13>=AY1),--(C13:Z13<=AY2),OFFSET(C196:Z196,MATCH(A197,A197:A203,0),,))

The reason you'd get #N/A with the other formula is if there was no matching
date.

Biff

"rhani111" <rhani111.2ccu9j_1155264613.7627@excelforum-nospam.com> wrote in
message news:rhani111.2ccu9j_1155264613.7627@excelforum-nospam.com...
>
> Hi Biff,
>
> The dates in C13:Z13 are 31/07/06 01/08/06 03/08/06 ect
>
> They are never in order because they may work 4days on and then 4off.
>
> The dates in AY1 is 31/07/08
> AY2 is 03/08/06
>
> The start and end dates change whenever the user enters the start and
> end dates.
>
> I need the forumla to look ON these days AND between them. So far the
> formula only looks for the EXACT dates and returns N/A if i was to
> enter say start date of 01/08/06 and an end date of the 04/08/06
>
> Hope that clears it up....lol
>
> Rhani
>
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile:
> http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=570557
>