COUNTIF on dates

[MartyB]

Hi,

I have two different worksheets.

One has dates recorded as dd-mmm-yyyy (e.g., 28-Nov-2009).

On the other, I have to create a lot (over 1000) of countifs against the months/years of these dates. So, for example, if there are several dates on the first workbook that fall in Nov-09, i want to do a countif on the second workbook that tells me how many Nov-09 dates there are.


Since I have to create so many of these, however, I'd like to make the countif formula simple enough to copy and drag.

Any ideas?

[daddylonglegs]

Hello MartyB

If dates to count are in sheet1 A2:A1000 then you can do that like this:

Put any date in the month to count in the other worksheet in A2 and then this formula in B2

=SUMPRODUCT(--(TEXT(Sheet1!A$2:A$1000,"mmmyy")="TEXT(A2,"mmmyy")))

copy down for other months

[MartyB]

Hey,

Thanks for the response, but unfortunately I didn't really understand what you wanted me to do in the second worksheet.

I have attached example worksheets with some data to show what i want to do.

Thanks again,
Marty

[daddylonglegs]

Hello Marty,

There are several ways to do this. I changed the approach a little.....if you put dates in B1:M1 it's probably a little easier. I put in the first of each month and then formatted as mmm-yy......then I used this formula in B2 copied across

=SUMPRODUCT(--(Sheet1!$B3:$B200-DAY(Sheet1!$B3:$B200)+1=B$1))

see attached

[rwgrietveld]

Sheet2!B1

Please Login or Register  to view this content.

Copy to the right till december

Sheet2!B2

Please Login or Register  to view this content.

It is long as the data have empty cells in them !

[MartyB]

Unfortunately, these two solutions don't seem to be working for me when I copy them into the original worksheets. In reality, the sheets are in two different workbooks, but i'm replacing the "Sheet1" with the correct workbook title and sheet.

Is there any way to do this without the SUMPRODUCT formula? It really seems too complex for what i wish to do. I simply want to do a change to text in the first sheet and then a simple countif on the second.