Countif formula

[Guest]

I have one column of different date ranges (January through to December)

I would like to use the countif formula to tell me how many occurrences of a date would fall into : Jan, Feb, Mar and so on.

My main trouble is I need to specify a range of dates in the countif formula (i.e. coutif A1: A3243, where dates are between 01/01/02 and 31/01/02)

Any thoughts !!!

Graeme Bell

 

[Aladin Akyurek]

On 2002-03-01 01:59, Anonymous wrote:
I have one column of different date ranges (January through to December)

I would like to use the countif formula to tell me how many occurrences of a date would fall into : Jan, Feb, Mar and so on.

My main trouble is I need to specify a range of dates in the countif formula (i.e. coutif A1: A3243, where dates are between 01/01/02 and 31/01/02)

Any thoughts !!!

Graeme Bell

Graeme,

COUNTIF cannot handle such complicated (multiple) conditions.

Lets say that A2:A30 houses the dates.

In D2 on enter the list of months:

{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}

In E1 enter: 2002 [ the year of interest ]

In E2 enter:

=SUMPRODUCT((YEAR($A$2:$A$30)=$E$1)*(TEXT($A$2:$A$30,"mmm")=D2))

Copy down this till the row of Dec.

Aladin

 

[Guest]

Cant get this to work - It just brings up a zero

I forwarded to your email a sample of the XL sheet, as i'm probably doing something stupid !!

Graeme.

 

[Aladin Akyurek]

On 2002-03-01 05:09, Anonymous wrote:
Cant get this to work - It just brings up a zero

I forwarded to your email a sample of the XL sheet, as i'm probably doing something stupid !!

Graeme.

Graeme,

When you see a list like

{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}

[ as the one I specified (they are called constant arrays in Excel) ],

copy it,

activate the start cell mentioned (I said D2 I believe),

go to the Formula Bar,

type =,

paste what you copied,

hit enter,

activate the start cell,

select from that cell on an area as big as the list mentioned (in your case 12 for the months of the year),

hit control+shift+enter,

copy selected range,

do a Edit|Pate Special >values.

So you have the specified items in Excel without typing them yourself.

PS. I think the intend of this part of instruction was the main hurdle for you to get the formula work (BTW, see your mail).

Aladin