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!

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!

Thanks Biff. I checked and that works. I ended up using two COUNTIF
functions instead:

=countif(tablerange,"="&datecell)-(countif(tablerange,"="&datecell+4)

so it gave me the total number of public holidays greater than the week
commencing date minus the total number of public holidays greater than the
friday of that week.

"T. Valko" wrote:

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!

Yeah, now that I think about it, that'll work and is simpler.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Josh Craig" wrote in message
...
Thanks Biff. I checked and that works. I ended up using two COUNTIF
functions instead:

=countif(tablerange,"="&datecell)-(countif(tablerange,"="&datecell+4)

so it gave me the total number of public holidays greater than the week
commencing date minus the total number of public holidays greater than the
friday of that week.

"T. Valko" wrote:

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!