MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Advanced Filter


Posted by dave on February 15, 2002 11:06 AM

Want to count number of times a month ("dec") appears in a column. The cells within the column are formatted as a dates.


Posted by Bob Umlas on February 15, 2002 11:15 AM

ctrl/shift/enter:
=SUM((MONTH(A1:A100)=1)*1)

Posted by Mark W. on February 15, 2002 11:15 AM

Suppose that your date field is in column A and
the 1st row of data (not headers) is row 2.
Enter =MONTH(A2)=12 into an unused cell and
leve the cell above it blank. Choose Advanced
AutoFilter and enter as a Criteria the 2 cell
range where you entered the formula above
(including the blank cell above it). This is
a computed criteria... that don't have a
field name in their 1st row!

Posted by Mark W. on February 15, 2002 11:20 AM

Just realized...

...that my response was predicated on your subject
line... If all you want to do is count them
then you could use...

=SUM((MONTH(A1:A10)=12)+0)}

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.

Posted by Mark W. on February 15, 2002 11:20 AM

Repost: Just realized...

...that my response was predicated on your subject
line... If all you want to do is count them
then you could use...

{=SUM((MONTH(A1:A10)=12)+0)}

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.