Click here for important information about Computing.net.

Hi,

Please help - I'm sure this has already been asked and answerred but I'm really struggling to find the answer I understand.In Excel - I'm trying to get a column of dates to add up and tell me how many date = Jan, how many = Feb and so on. Example. Dates column is formatted like 01-Jan-10. If there are 15 entries of Jan then I want the system to report back in another cell 15, if Feb had 24 enteries then 24 reports back and so on throughout the year.

Thanks for any help! I'm sure I'm going to kick myself when I find out the answer - but thanks anyway!

What kind of entries are you talking about?

There's probably some built-in Excel functions you can use, but I'll show you the VBA way:

1) Add/Pick a (new) VBA Module

2) In that module, paste the following code:Function CountIfSucks(r As Range, m As Integer) As Long Dim c As Range For Each c In r CountIfSucks = CountIfSucks + (1 And (Month(c) = m)) Next 'c End Function

3) In the desired cell, type:=CountIfSucks(<range>,<number of month>)

Jan = 1, Feb = 2 . . . Dec = 12For the less VBA way, your best bet is the Office forum. I'll send this thread there.

Ok sorry maybe not quite a good description on start-up! All that I have, on sheet1 a column where a date stamp is entered. eg. 01/01/2010.(next cell we type blar blar blar this happened... now there might be 10 lines happen in Jan but all with different dates. then Feb comes along and the same thing happens again

At the end of all this - I want a cell for each of the months to count how many accuracies happened in Jan, how many in Feb and so on.Pauls / Razor thanks for your help(s)

Hi, Here is one way to do this.

Put dates in column A. For this example I had dates in cells A2 to A24.

(You can change the location of the dates later, once this is working).To get the 12 months as headers:

In Cell B1 enter 01-Jan-2010 and format in a suitable date format

In Cell B2 enter the formula =EOMONTH(B1,0) and format in a suitable date format

In Cell C1 enter the formula =B2+1

In Cell C2 enter the formula =EOMONTH(C1,0)

Select cells C1 and C2 and extend the formulas out to column M by dragging or copy and paste.

Cell M2 will now have 31-Dec-2010Now the formula that does the real work:

In cell B3 enter this formula: =SUMPRODUCT(($A$2:$A$24>=B1)*($A$2:$A$24<=B2))

Note the $ signs

Extend this formula out to Column MThe number of dates falling in each month will now be in row 3, under each date range.

Regards

Hi, You can also do this with just one row for dates. If Cells B1 to M1 contain the dates for the first of each month, then replace the formula in B3 with this:

=SUMPRODUCT(($A$2:$A$24>=B1)*($A$2:$A$24<=EOMONTH(B1,0)))

and extend the formula out to column M as before.

Format the dates in Row 1 with "mmmm" to just show the month.

(Just entering month names in row 1 won't work - you have to start with full dates for the first of each month.Regards

PS If anyone using Excel 2003 tries this, please note that the EOMONTH() function is not installed by default. You have to have the Analysis ToolPack Add-in loaded).

Guys - Think we're trying to complicate all of this. Maybe its me and my usless decriptions.

Does this help?

"Cell C5"

02/Jan/10

03/Jan/10

04/Jan/10

05/Jan/10

06/Jan/10

06/Jan/10

06/Jan/10

09/Feb/10

10/Feb/10

11/Feb/10

12/Feb/10

06/Mar/10

06/Mar/10

09/Apr/10

10/Apr/10

11/May/10

12/May/10

13/May/10Then on another sheet I have date heading across the top.

Jan Feb Mar April May etc...This is what I need...based on the dates above, I need formula that will look through the dates and work out how many are in Jan, how many are Feb, Mar...so on. Looks like this.

Jan | Feb | Mar | Apr | May |

7 | 4 | 2 | 2 | 3 |

Hi, If you use the formula in Response #5, as long as your list of Months across the top of the 'other sheet' are real Excel dates, it will work.

Instead of Jan Feb etc

enter 01-Jan-2010 01-Feb-2010

Then format them as "mmm" so that they still show as Jan Feb etc.Under each month enter:

=SUMPRODUCT(($A$2:$A$24>=B1)*($A$2:$A$24<=EOMONTH(B1,0)))

where B1 contains the month

Change $A$2:$A$24 to point to your column of dates on the first sheet e.g. Sheet1!$C$5:$C$50Regards

Hi, With your dates in cells A2 to A19 on Sheet1

Here is my result on Sheet2:

A B C D E 1 Jan Feb Mar Apr May 2 7 4 2 2 3

Cell A1 had the date 01-Jan-2010 entered and formatted as "mmm"

Cell A2 is:=SUMPRODUCT((Sheet1!$A$2:$A$19>=A1)*(Sheet1!$A$2:$A$19<=EOMONTH(A1,0)))Regards

All - I've tried all the options you've all giving to me and found Response Number 2 from Razor2.3 seems to work best. Although It gives me an issue with Dec's feedback and give me numbers like 170 for Dec even though there's no dates for Dec. Anyhow, its working for all the other dates. Thanks for you time / Efforts for me. All the best for 2010

WiReLaD

oops Sorry Humar didn't see your reply until after my last post. That's exactly what I'm after and looks easier to do!! I'll give this option another looking at - This line EOMONTH - this worries me. If I do a lookup within my functions I don't see this as an option? I'm using office 2003.

Cheers buddy

WiReLaD

By-George I think we have it!!!! All working fine - Because I'm using Office 2003 the EOMONTH did start being used until 2007. However, there is an Add-on for it and now all works great.

This is a deffo close call now - Its all working and happy.

Really fella's, thanks for your(s) time

Cheers

WiReLaD

Ask Your Question

Weekly Poll

Do you think Intel's new hybrid chips will better compete with Apple/ARM?

Discuss in The Lounge

Poll History