Array Query

[Richiejjj]

Hi

I am building a model which has a named range - bank_holidays - which lists down all of the bank holidays between now and December 2015.

I then have a series of start dates (c7:c1681) and end dates (g7:g1681)

To then work out how many bank holidays there are in each start to end date (each a year long) I tried the following array:

{=SUM(IF(Bank_Holidays>0,1,0))-SUM(IF(Bank_Holidays>G7,1,0))-SUM(IF(Bank_Holidays<C7,1,0))}

Now the problem I have is that the cell references G7 and C7 are not increasing down the rows.

Can someone help?

Rich

 

[vaskov17]

not array entered:

=SUMPRODUCT((bank_holidays>=C7)*(bank_holidays<=G7))

 

[Richiejjj]

thank you very very much

 

[n0gel]

OR, to continue with what you were doing, and mine is really exactly the same as the sumproduct formula except it is array entered. Both the sumproduct and my version are array formulas. Sumproduct just is built in and does not require the cse confirmation.

{=SUM(IF((Bank_Holidays>=C7)*(Bank_Holidays<=G7),1,0))}

 

[Richiejjj]

OK, thanks - that's great