Hi all,
I want to be able to count cells if they contain a certain month.
For example, say i had the following date and times
A
10/01/2009 09:30
10/01/2009 09:30
10/02/2009 09:30
10/03/2009 09:30
10/03/2009 09:30
10/09/2009 09:30
10/09/2009 09:30
10/09/2009 09:30
10/12/2009 09:30
How would i count how many are in the month of February?
Thanks in advance!
Last edited by VBA Noob; 02-10-2009 at 09:39 AM.
Is the year relevant - ie distinguish between Feb 08 and Feb 09 ?
Last edited by DonkeyOte; 02-10-2009 at 05:58 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
It the moment no but perhaps further down the line it will be!
Thanks!
You can use this:
=SUMPRODUCT((A1:A4>39844)*(A1:A4<39873))
This will do feb 09
But if you want to find another month all you have to do is replace the numbes in bold eg for jan 09 > 39813 < 39845
You can find these numbers by typing in the date for the day before and the day after the range that you want and then changing the format to number. You can do this for any date but make sure you put the year in as well.
Then change the format for the cell with the formula to number, other wise you will get a date
Hope this helps
Examples:
Year not relevant: Cell C1 contains Date 1st Feb 2009
=SUMPRODUCT(--(MONTH(A1:A10)=MONTH(C1)))
Year relevant: Cell C1 contains Date 1st Feb 2009
=SUMPRODUCT(--(INT(A1:A10)-DAY(A1:A10)+1=C1))
Hi all,
I'm struggling to get either of these options to work?
Any further help would be appreciated!
Cheers
The formulae provided would work with the sample data (assuming for my ex's you did enter the Feb 1 date into C1) -- if you can't get the functions to work with your real data you will need to provide a sample else we're all just going to be guessing I'm afraid.
Apologies,
I have now got it working but it appears to be counting blanks.
What purpose do the "--" have at the start?
Thank you for your continued assistance!
Sample attached!
Chris
Use the other approach offered, namely:
MONTH will coerce a blank to 0 and day 0 is 0 Jan 1900 (ignoring 1904 Date System)... ie MONTH 1.
Please Login or Register to view this content.
You would need to use an additional qualifier to exclude the blanks, eg:
-- is double unary operator.. used to coerce Booleans to Integer.Please Login or Register to view this content.
Run a search on this forum for more explanations... I've been asked three times today already ;-)
Also for all things Sumproduct review Bob's site:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
(the above was offline for a while but I think is now back up again ?)
Last edited by DonkeyOte; 02-10-2009 at 08:33 AM. Reason: missing parentheses
Hi CJConnor,
Try this formula, it is entered as an array formula (ctrl+shift+enter).
=SUM(IF($A$1:$A$15>=D3,IF($A$1:$A$15<=EOMONTH(D3,0),1,0),0))
What about COUNTIF
2 points (other than the solution offered already works)Originally Posted by emanon132501
a) a CSE array is regarded as slower than a Sumproduct
b) EOMONTH requires AnalysisToolPak be activated - please always be sure to inform OP of this if using ATP function.
EDIT - 3 points...
3rd point... if you insisted on using a CSE array could simplify somewhat to:
The * will coerce the Booleans, such that TRUE * TRUE = 1 ... no need for IF approach in this instance.Please Login or Register to view this content.
Last edited by DonkeyOte; 02-10-2009 at 10:01 AM.
Perfectly valid you would however need to use 2 of course, ie
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks