combining COUNTIF and OR functions

[Josh Craig]

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!

 

[T. Valko]

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))

 

[Josh Craig]

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]

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

Thanks for the feedback!