Countif formula

G

Guest

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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top