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:

Date
03/01/2012
03/01/2012
05/01/2012
07/02/2012
07/02/2012
07/02/2012
02/05/2012
etc..

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?

Report •

✔ Best Answer
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?
or
You 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/830287


MIKE

http://www.skeptic.com/


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 error


Code:


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.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
September 20, 2012 at 15:48:18
✔ Best Answer
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.


Report •

Ask Question