How do I get the COUNTIF criteria to recognize only month and yea.

O

Omega

How do I get the COUNTIF criteria to recognize only month and year on a range
with specific dates?
 
D

David McRitchie

Watch out for line breaks
there are two ">=" in both formulas
=COUNTIF(A:A,">="&DATE(YEAR($C$1),MONTH($C$1),DAY(1))) - COUNTIF(A:A,">="&DATE(YEAR($C$1),MONTH($C$1)+1,DAY(1)))


=SUMIF(A:A,">="&DATE(YEAR($D$1),MONTH($D$1),DAY(1)),B:B) - SUMIF(A:A,">="&DATE(YEAR($D$1),MONTH($D$1)+1,DAY(1)),B:B)

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
A

Aladin Akyurek

=SUMPRODUCT(--(TEXT(A2:A5,"mmm-yy")=TEXT(E2,"mmm-yy")))

where E2 houses a true date value with the date component set to 1 lik
11/1/04.
 
A

Aladin Akyurek

Harlan said:
Why convert to text?

=SUMPRODUCT(--(A2:A5-DAY(A2:A5)=E2-DAY(E2)))
Click to expand...

That's an interesting idiom in that it should be robust/hassle-fre
across differing date formats
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

countifs Function not giving desired result with date criteria 2
Using <= in countifs statement 1
COUNTIFS with No Criteria 1
countif with 2 criteria 4
HOW TO INPUT MONTHLY ACCUMULATED SALARY IN MS PROJECT? 0
countif and sumif 3
COUNTIFS with a date for criteria... 1
SUMIF with criteria "<>" & "=" 4

Top