Count cell if it contains a date within a range

gpzbc

New Member
Joined
Feb 22, 2011
Messages
10
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:

EntityMeeting Date
Entity#15/1/13, 4/10/13, 11/10/12, 9/14/13
Entity#26/3/13, 7/21/13
Entity#311/10/13
Entity#48/12/13, 8/4/13, 1/2/13, 3/23/13

<tbody>
</tbody>


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.
 
Last edited:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,409
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Feb 22, 2011
Messages
10
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,409
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Feb 22, 2011
Messages
10
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top