Solved Count the number of entries per month in Excel?

September 14, 2012 at 14:51:20
Specs: Windows XP
 Hi everyone,I currently have data as follows in Excel:Date03/01/201203/01/201205/01/201207/02/201207/02/201207/02/201202/05/2012etc..Basically, I have a column with all my date ranges from 01/01/2012 to 31/12/2012 with some having multiple same values as there was different data on those dates that are captured in other columns.Currently, I'm using the following to calculate January: =SUMPRODUCT(--(CEO!A\$2:A\$378<>""), --(MONTH(CEO!A\$2:A\$378)=1))This formula works great, but the issue is the Range.. I have it hard set from A2 to A378 but in-reality, the spreadsheet will keep growing and A378 will probably end up much higher.Instead of trying to hardcap at some random higher value, I was wondering if it's possible to use a Macro to insert a similar type of formula into the cell to calculate it where I can just input the last known row as the last cell value?Thanks in advance!

See More: Count the number of entries per month in Excel?

September 20, 2012 at 15:48:18
 The problem is with your quotes "" VBA sees that as the end of a string and when it tries to put the formula in a cell, the formula is incorrect and Excel throws an error back to VBA.Try this:.FormulaR1C1 = "=SUMPRODUCT(--(CalcDate<>""""), --(MONTH(CalcDate)=1))"Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

#1
September 14, 2012 at 16:34:03
 Have you thought of using a Named Range?orYou can define a Dynamic Named Range that will allow you to insert new data.See here for a pretty good example: http://www.exceluser.com/explore/dy...Here is how MS does it:http://support.microsoft.com/kb/830287MIKE

Report •

#2
September 20, 2012 at 14:21:37
 Thanks for the suggestion it did help for all the messy info i had. Instead of all the CEO!Range stuff it's just CalcDate now which helps alot for reading it. :)Unfortunately, when I try and toss it into a macro to run on a cell it still tosses an error message stating the following:Run-time error '1004':Application-defined or object-defined errorCode:Sub UpdateDash()Set dash = Sheets("Dashboard")dash.Range("B10").FormulaR1C1 = _ "=SUMPRODUCT(--(CalcDate<>""), --(MONTH(CalcDate)=1))"End Sub

Report •

#3
September 20, 2012 at 14:53:13
 Sorry, my Macro skills are just above nil.Hopefully someone else will have a solution.MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
September 20, 2012 at 15:48:18