Tom, JMB,

Thanks for replying, i can perform fairly routine vba programming but
any more than simple is a bit beyond me. To get my named range i
highlighted the area i wanted and then in the window next to the
formula bar gave it a name, the first range is called Hols1 and this
=Holidays!$A$14:$AK$133 is the range, i use the ranges like this
=COUNTIF(Hols1,E2)*8+COUNTIF(Hols2,E2)*12+COUNTIF( Hols3,E2)*8+F2 on
another sheet where hols2 & 3 represent the other 2 portions of the
year.

So i would like know how to change the "hols" for something where it
would only count if the value of E2 is found between and including 2
dates i.e count for as many times if "eric" (the value of E2) is found
between 1/1/06 & 15/4/06 inclusive and so on...

the countif stuff i did above seemed fairly simple, but the rest is
beyond me!

regards,

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=511571

So adjusting the named range is not the end result you want? Sounds like you
want a multiple condition test.

Assuming that your table looks like:

A B
12/31/05
1/1/2006 eric
2/1/2006 joe
4/15/2006 eric
5/15/2006 eric
3/15/2007 bob


and cell E2 = eric


=SUMPRODUCT(--(B1:B6=E2),(A1:A6=DATE(2006,1,1))-(A1:A6DATE(2006,4,15)))

would give you 2. With this formula, it doesn't matter if the data is
unsorted.

With Sumproduct, you cannot use entire columns (such as A:A, but A1:A65535
works fine - unless you're on an old version of excel that has fewer than
65536 rows).






"Simon Lloyd" wrote:


Tom, JMB,

Thanks for replying, i can perform fairly routine vba programming but
any more than simple is a bit beyond me. To get my named range i
highlighted the area i wanted and then in the window next to the
formula bar gave it a name, the first range is called Hols1 and this
=Holidays!$A$14:$AK$133 is the range, i use the ranges like this
=COUNTIF(Hols1,E2)*8+COUNTIF(Hols2,E2)*12+COUNTIF( Hols3,E2)*8+F2 on
another sheet where hols2 & 3 represent the other 2 portions of the
year.

So i would like know how to change the "hols" for something where it
would only count if the value of E2 is found between and including 2
dates i.e count for as many times if "eric" (the value of E2) is found
between 1/1/06 & 15/4/06 inclusive and so on...

the countif stuff i did above seemed fairly simple, but the rest is
beyond me!

regards,

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=511571

JMB, again thanks for replying, i understand the formula you gave and
can see it will work fine for counting instances of the value in
whichever cell the test is in, but in the formula i use
=COUNTIF(Hols1,E2)*8+COUNTIF(Hols2,E2)*12+COUNTIF( Hols3,E2)*8+F2 i
need to multiply each instance by either 8 or 12 depending where it
finds the instance (this is because it is counting up the amount of
hours someone has booked off), i can of course mail you the workbook if
you would like to see what i'm getting at!. I am using excel 2003 and
win xp.

My e-mail address is simonwlloydATmsnDOTcom, if you want to mail me
direct i can send you the workbook by return.

Thanks,

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=511571