Using COUNTIF or SUMIF to evaluate a date range

michael.dilios15206

Hi, so I have a ColumnA that has random dates for the current year 2017.  The column is formatted as a "Date" style column.

My goal is to sum each date in this ColumnA by Month:

Example:

So to the right of each date, I want the sum of all dates that are in "January" that are in Column A.  

 

Thanks

 

 

 

 

Adam Overton

I have some previous answers, so these are based on that. For an arbitrary date range, the formula looks something like this:

=SUMIF([Due Date]1:[Due Date]6, AND(@cell >= DATE(2017, 4, 1), @cell < DATE(2017, 5, 1)), Cost1:Cost6)

Note that DATE(2017,4,1) could be substituted for a reference to a start date cell.

Similarly for the end date.

For month by month like you actually asked, you can do something like this:

=SUMIF([Due Date]1:[Due Date]6, AND(MONTH(@cell) = 4, YEAR(@cell) = 2017), Cost1:Cost6)

You can see I check for MONTH(@cell) = 4, and that just means April of course. If you had this refer to a month number in another column, you could fill this down and get what you want.

 

michael.dilios15206

Thanks for the reply.  I am getting "Invalid Data Type::

My formula:

=SUMIF([Evaluation Date (12-month followup needed)]1:[Evaluation Date (12-month followup needed)]16, AND(MONTH(@cell) = 4, YEAR(@cell) = 2017), Count1:Count16)

 

I am assuming your "Cost1:Cost6" was a separate column that had numbers for sum?  I changed mine to "Count".

Also, the date column is a link from another sheet.  I am doing this to create a reference sheet for a Sights widget.

 

 

 

Adam Overton

Yes, Cost was another numeric.

The formula doesn't seem to work if you have blanks in the list. I don't think the date functions like that.

I think we can wrap the condition in "IFERROR" and then return false if it isn't a date.

=SUMIF([Evaluation Date (12-month followup needed)]1:[Evaluation Date (12-month followup needed)]16, IFERROR(AND(MONTH(@cell) = 4, YEAR(@cell) = 2017), false), Count1:Count16)

 

michael.dilios15206

This worked perfectly.  Thank you!  

michael.dilios15206

Hi, I have a new request.

I am looking for a formula so I can use a widget that displays total completed projects for just 2017 using the count or sum formula.  I am using a reference sheet for the widget and linking from another sheet to access data.

In the attached picture, I want to count just the Completed from the Status column that have a 2017 "Finish" column Date.  Basically, excluding any Finish date from prior years.  

Thank you for any help.  

 

 

 

Adam Overton

Try this:

=COUNTIFS(Status1:Status8, "Complete", Finish1:Finish8, NOT(ISBLANK(@cell)))

It's better to post a new question so everybody can see it and have a chance to respond, as I might miss this!

michael.dilios15206

Thank you for the reply.  This works, except I need to just count Completes with a Finish column date of 2017.  That Finish column also has 2016 completes in it that I do not want counted.

Also, I will add any other questions to a new thread.  Thanks again for any help!  

Adam Overton

change the NOT(ISBLANK(@cell)) to YEAR(@cell)=2017

 

michael.dilios15206

No luck:

 

My formula

 

=COUNTIFS(Status1:Status500, "Complete", Finish1:Finish500, YEAR(@cell) = 2017)

 

 

J. Craig Williams

Just as previously, the formulas do not like blank dates.

Try this:

=COUNTIFS(Status1:Status500, "Complete", Finish1:Finish500, IFERROR(YEAR(@cell) = 2017, false))

Craig

michael.dilios15206

Thank you.  It works! 

michael.dilios15206

Hi, your solution to my last issue works perfectly, but I have a new ask:

How do I get that formula to also scan a second column for the same criteria as well?  For example, the second column to also check is named "Revised Finish".

Current working formula:

=COUNTIFS(Status1:Status500, "Complete", Finish1:Finish500, IFERROR(YEAR(@cell) = 2017, false))

Thank you for any information!

 

MollyG

I am trying to do something similar but can't get this to work.

I want to check all dates for items created in the current month. 

=SUMIF(Created:Created, AND(MONTH(@cell) = 3, YEAR(@cell) = 2018)

What am I doing wrong? And can I make this a formula that understand what month it is instead of having to update the month number each month?

I also tried:

=COUNTIFS(Created:Created, AND(MONTH(@cell) = 3, YEAR(@cell) = 2018))