Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
=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
|
|||
|
|||
"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
|
|||
|
|||
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 |
Display Modes | |
|
|