COUNTIFS between dates & criteria.

jjimenez55506

Greetings Smartsheet Community,    

I am attempting to cross reference another sheet and Count all the Completed tasks within a certain time frame. 

 

So far I am successful counting the dates in the range using the following formula:

=COUNTIFS({Range 1}, <=DATE(2018, 4, 31), {Range 1}, >=DATE(2018, 4, 1))

 

Next I'd like to add "AND" and "OR" ( to count all tasks "Completed") but I keep running into errors. 

 

Does anyone have a solution?

Any advice would be greatly appreciated.

 

Thank you!

 

Josh

L_123

A:A = Range of dates

B:B = Range of completion

 

=Count(collect(A:A,A:A,or(@cell&lt;= Date(2018,4,31),@cell >= Date(2018,4,1)),B:B,"Completed"))

jjimenez55506

Thanks Luke, I figured it out.

Here's What I did:

=COUNTIFS({Start Date}, <=DATE(2018, 5, 31), {Start Date}, >=DATE(2018, 5, 1), {Stats}, "Completed")

For Completed

and 

=COUNTIFS({Start Date}, <=DATE(2018, 5, 31), {Start Date}, >=DATE(2018, 5, 1), {Stats}, <>"Completed")

 

For Not Completed

 

Kelly Gabel

Trying to use a similar formula and coming back with the wrong count. Can someone help me understand what's wrong?

=COUNTIFS({Estimated Mech Completion Date 1}, <=DATE(20, 10, 1), {Estimated Mech Completion Date 1}, >=DATE(20, 12, 31))

That's the exact formula I'm using. I'm trying to get a count of all rows in this range where the date is between 10/1/2020 and 12/31/2020. My "year" portion of the formula isn't 4 digits because my source sheet isn't using the 4 digit year...but I have tried it both ways with no luck.

@Paul Newcome just tagging you because you rock with formulas!

Thanks!

Paul Newcome

@Kelly Gabel If your dates are in a date type column then you are going to still want to use the 4 digit year in your DATE functions. If they are not in a date type column, we would need to do some things a little differently.

Kelly Gabel

@Paul Newcome They are in a date type column, but using the 4 digit year still didn't work in that formula...

Paul Newcome

Sorry. I completely missed this part...

Switch your dates around. Your formula is currently saying to count dates that are less than 10/1 and greater than 12/31.

You want greater than 10/1 and less than 12/31.

Kelly Gabel

Thanks @Paul Newcome That fixed it!

Paul Newcome

Happy to help. 👍️

Megan Harry

@Paul Newcome

Hi Paul,

I would love your help with a formula if you don't mind. You helped me the other day with a similar formula and I was so thankful!

I am trying to to do a count ifs formula to know the count between two dates and another criteria.

For example, I have a Company column and I want to know how many times "Contractor" is listed but only when the date is between Jan 1 and March 31st. So dates greater than 2020/1/1 and 2020/3/31.

Can you please help me? Thank you so much, and let me know if I need to provide greater detail.

Paul Newcome

@Megan Harry You could use something like this...

=COUNTIFS(Company:Company, "Contractor", Date:Date, AND(@cell >= DATE(2020, 01, 01), @cell <= DATE(2020, 03, 31)))

Megan Harry

@Paul Newcome Yay thank you so much Paul! I was so close with the formula I was trying, but you helped me get it! I am very appreciative! It worked perfectly!

Paul Newcome

@Megan Harry Happy to help. 👍️

Michelle Bestercy

@Paul Newcome

Hi Paul,

I'm attempting to use your formula referenced on 10/16/20 but am referring to 2 separate data ranges within that formula and am receiving an "Invaid Data Type" error:

=COUNTIFS({RANGE 1}, [Category1]@row, AND({RANGE2}, AND(@cell >= DATE(2017, 8, 28), @cell <= DATE(2018, 8, 26))))

I have found the following formula to be successful:

=COUNTIFS({RANGE2}, AND(@cell >= DATE(2017, 8, 28), @cell <= DATE(2018, 8, 26))) but I wish to add references to those 2 separate data ranges.

Can you please provide any insight into why I receive an error when adding references to data ranges?

Thank you!

Paul Newcome

@Michelle Bestercy You do not need the first AND statement.

=COUNTIFS({RANGE 1}, [Category1]@row, {RANGE2}, AND(@cell >= DATE(2017, 8, 28), @cell <= DATE(2018, 8, 26)))

Michelle Bestercy

@Paul Newcome

Thank you so much, Paul! That solution worked for me.