Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Omega
 
Posts: n/a
Default How do I get the COUNTIF criteria to recognize only month and yea.

How do I get the COUNTIF criteria to recognize only month and year on a range
with specific dates?
  #2   Report Post  
David McRitchie
 
Posts: n/a
Default

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

"Omega" wrote in message ...
How do I get the COUNTIF criteria to recognize only month and year on a range
with specific dates?



  #3   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


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

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

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



--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=275710

  #4   Report Post  
Harlan Grove
 
Posts: n/a
Default

"Aladin Akyurek" wrote...
=SUMPRODUCT(--(TEXT(A2:A5,"mmm-yy")=TEXT(E2,"mmm-yy")))


Why convert to text?

=SUMPRODUCT(--(A2:A5-DAY(A2:A5)=E2-DAY(E2)))


  #5   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


Harlan Grove Wrote:
"Aladin Akyurek" wrote...
=SUMPRODUCT(--(TEXT(A2:A5,"mmm-yy")=TEXT(E2,"mmm-yy")))


Why convert to text?

=SUMPRODUCT(--(A2:A5-DAY(A2:A5)=E2-DAY(E2)))


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


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=275710

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 09:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"