COUNTIFS using date as condition

[aijp]

Hello,

I'm having quite a bit of trouble with this one! : I want to write a formula that checks the number of dates that fall within a month (of the same year). What I have now is in column A of sheet1 is a list of month/years from "May-11" to "May-14" (A5:A41); in sheet2 I have a bunch of entries that were entered on various dates(A1:A1100). This is the formula I have come up with, which is written in a cell in sheet1:

Please Login or Register  to view this content.

Here, the idea is that it looks for dates ranging from May 1 2011 (may 2011 = A5) to May 31 2011 (june 2011 = A6). However, it doesn't work!!! There seems to be a problem with the "criteria" part of it, where it doesn't seem to accept the Value function with the <>=

How does one fix this? or is there an alternative method? thanks!

[daffodil11]

Put your arguments in quotes, and concatenate the dates:

=COUNTIFS(Sheet2!$A$1:$A$1100,">="&A5,Sheet2!$A$1:$A$1100,"<"&A6)

I'm not quite sure what part the VALUE function you're using is trying to do. Simple date comparison should work fine. (I guess your dates are in text?)

[alansidman]

It would be very helpful if we could see a sampling of your spreadsheet to do full analysis.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[aijp]

You are a very beatiful and helpful flower, daffodil11. It worked perfectly!

The "value" was there because I was trying anything to get it working

Alan, imagine sheet1, column A like:

May-11
June-11
July-11

...and so on til may-14. Sheet2 column A

11-May-03
11-May-14
11-May-15

...and so on til the present day. There are many dates, some of them repeat if info was entered on the same day. More dates will be added until next year! I need to track how many have been entered in a given month, is all, and figured this would be the best method.

Anyway, I see what you're saying about attaching a sample workbook, I'll do it next time!