O
Omega
How do I get the COUNTIF criteria to recognize only month and year on a range
with specific dates?
with specific dates?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
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
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.
where E2 houses a true date value with the date component set to 1 lik
11/1/04.
H
Harlan Grove
Aladin Akyurek said:=SUMPRODUCT(--(TEXT(A2:A5,"mmm-yy")=TEXT(E2,"mmm-yy")))
Why convert to text?
=SUMPRODUCT(--(A2:A5-DAY(A2:A5)=E2-DAY(E2)))
A
Aladin Akyurek
Harlan said: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-fre
across differing date formats