Counting Data

Topic

New Reply

Excel 2000.

Hello,

I will attempt to make this simple….

A2..A500 contains data (each cell WILL contain data)
M2..M500 will contain DATES (NOT every cell will contain a date)

For example: M5 might have 6/24/01 but M6..M10 wont contain a date..M11 will have 6/25/01.

What I am attempting to do is create a formula (or function) and put it in N5..therefore when N5 sees there is a date in M5 start counting all the values beginning with A5 and stop counting at A10 because the date changes in M11 …in this case the count (total) in N5 would be 5. The count starts over beginning in A11 because the new date is in M11…and so on.

If this is too confusing, please post back and I will try to make it more clear.

I appreciate any suggestions.

Reply | Quote

Replies

Roberta,

The following will do what you want (or what I think you want), provided that all the cells between the different dates are blank, and contain not even a space. A useful way to ensure that there are no spurious spaces in a rang is to use conditional formatting that will highlight them, should they happen.

The formatting to apply is :
Cell Value Is greater than or equal to =” “, and then set the background color to something that will highlight the cell.

You can apply that conditional formatting to M1:M500, or whatever the extent of the relevant range is.

Function CountForDate(rng As Range) As Long
Application.Volatile
Dim rngAddr As String
rngAddr = rng.Address
CountForDate = (Range(rngAddr, Range(rngAddr).End(xlDown)).Count) – 1
End Function

Hope the above assists you,

Andrew

Reply | Quote

Good Morning Andrew,

I appreciate your help with this…one thing tho and that’s cause i’m a dummy…but what do I do with the code? I put it in vb and created a function but nothing is happening…

Thanks,

Reply | Quote

Did you place the code in a general module in the workbook involved ? To that you should go too the VB editor (Alt F11), and Insert, Module, and paste the code there. When doing that make sure the workbook you are working on is highlighted in the Project window (usually on the right). It will be referred to as VBAProject(Bookname), where Bookname is the name of the workbook involved.

In N2 you need to enter the formula =IF(M2″”,CountForDate(M2),””), and copy it down. It will be necessary to put some value in the last+1 cell in column M, e.g. if the last used row is 500, you need a value in M501.

Hope the above is clear to you.

Andrew

Reply | Quote

Good Morning,

I tried Andrew’s first and it worked perfectly…however, I wanted to test Brooke’s suggestion as well and it worked perfectly too.

Thanks to both of you for your quick and accurate solution.

Reply | Quote

Just in case you’re after a way to do this without code, the simplest method I can think of is to put “=IF(M20,M2,O1)” in o2 and “=SUMIF(O:O,M1,A:A)” in p2, then copy the two formulas down for the entire length of your data. You could hide colum o if you didn’t want to see these dates.

HTH

Brooke

Reply | Quote