Try this...

Entered in A14 and copied across:

=SUMPRODUCT(--(INDEX($B2:$D4,,MATCH(YEAR(A13),$B1:$D1,0))=A13),--(INDEX($B2:$D4,,MATCH(YEAR(A13),$B1:$D1,0))<=A13+6 ))

--
Biff
Microsoft Excel MVP


"Josh Craig" wrote in message
...
Hi,

So here is my problem. I am trying to create a worksheet function that
gives me all the public holidays in a given week. So i have two data
sources. One is a table of public holidays. Where each column is a year
and
each row is a different public holiday:

2009 2010 2011 etc
Christmas
Good Friday
Bank Holiday
etc

'christmas' is in cell A2 and '2009' is in cell B1

(NB: I've already worked out formulas to fill in all the public holiday
dates)

Secondly, I have a row of dates showing the first monday of each week:

27/4/09 4/5/09 11/5/09 18/5/09 etc

'27/4/09' is in cell A13

I want to put the number of public holidays in each week in the row
directly
below the row of dates.

for example, for the week over christmas: 21/12/09
2

The only way I can think to do this is to have some complex and unwieldy
combination of countif, if and or functions. Can anyone think of a
simpler
way? Any help is greatly appreciated!