possible countif formula?

[Mark D]

Hi Again everyone

I am trying to get a formula in (possible countif) which doesn't seem to be
working

In column D I have the following dates
D42 30/03/2009
D43 01/06/2005
D44 06/02/2009
D45 30/03/2009
D46 19/10/2009
D47 08/03/2010
D48 15/07/2010
D49 15/07/2010
D50 15/09/2010
D51 15/09/2010

In I3 I have 31/01/10, J3 28/02/10 etc etc running along to AR3 which has
31/12/12

I want to write a formula that simply says to check the dates in column D42
- D51 against the date in I3 and if the date in D42-51 is prior to I3 add 1,

So my first forumla for January should = 5, February = 5, March 6 etc tec

I have tried a countif forumula but it doesn't seem to work.

Any ideas appreciated.

Thank you

 

[Ashish Mathur]

Hi,

Try this formula and copy to the right

=countif($D$42:$D$51,"<="&I$3)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Mark D]

Hi Ashish

That's great thank you, works a treat, could I ask what the & is doing?

 

[Ashish Mathur]

Hi,

The & symbol combines/aggregates strings. For e.g. if you have AS in cell
D5 and DF in cell E5, in F5, enter =D5&E5 - your answer will be ASDF. Since
I3 has to be treated as a cell reference, it cannot be within double quotes
and therefore the cell reference and <= are combined with the & symbol

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com