Count cell if it contains a date within a range

[gpzbc]

I would be very appreciative of anyone's help on this. Here is what I am trying to do:

I have a column for reporting meeting dates for each entity. So a cell may contain numerous dates. For example:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Entity[/TD]
[TD]Meeting Date[/TD]
[/TR]
[TR]
[TD]Entity#1[/TD]
[TD]5/1/13, 4/10/13, 11/10/12, 9/14/13[/TD]
[/TR]
[TR]
[TD]Entity#2[/TD]
[TD]6/3/13, 7/21/13[/TD]
[/TR]
[TR]
[TD]Entity#3[/TD]
[TD]11/10/13[/TD]
[/TR]
[TR]
[TD]Entity#4[/TD]
[TD]8/12/13, 8/4/13, 1/2/13, 3/23/13[/TD]
[/TR]
</tbody>[/TABLE]


What I am trying to do is count the total number of meetings that all of my entities had within a certain month.

I imagine I need to use the COUNTIFS formula, but I can't seem to get it to search the cell for a particular value within a list of comma separated dates. Here is the COUNTIFS formula that I am trying to modify to search for a certain date within a cell that contains comma separated dates.

=COUNTIFS(AL$4:AL$323,">6/30/13",AL$4:AL$323,"<8/1/13")​

Thank you very much for your help. I have been working on this for a several days and can not figure it out.

 

[etaf]

try sumproduct

=SUMPRODUCT(($AL$4:$AL$323 > DATE(2013,6,30))*($AL$4:$AL$323 < DATE(2013,1,8)))<date(2013,1,8)))

</date(2013,1,8)))

Syntax for the date function is: DATE(Year, Month, Day)

 

[gpzbc]

Thank you very much for your reply. I tried it in my example table. I used:

=SUMPRODUCT((B2:B5 > DATE(2013,7,31))*(B2:B5 < DATE(2013,9,1)))​

This totaling the number of meetings for August and should yield a result of "2" but it unfortunately yields a result of "0". Is there something I did incorrectly?
Thanks again.

 

[etaf]

on your example table you have more than 1 date in a cell , in which case it will not work
the cell has to be a date format

note sure how you would do what you are after looking for that range in a text string

 

[gpzbc]

Thanks. Yes, the fact that I have multiple dates in each cell is what is proving to be problematic for me. I had the idea that I might be able to SEARCH for a date that falls within a range in each of the cells. If that is true, then COUNTIF the cell. But you are saying that probably not work?

 

[gpzbc]

In case anyone has a similar question and comes across this thread, I have found a solution with the help of the folks over at Excel Forum. Here is the thread that contains my solution. Count Cell if it Contains a Date Within a Range

Good luck!