Date Range Formula Help

Justin Tackitt

Can someone please help me to create a formula that returns the number of items with a "Permit Expiration Date" between 7/1/20 and 7/31/20 AND has the "360 Photos Have Been Submitted" unchecked? I've tried several "if and" formulas and they didn't work :(

Thank you!!

Mark Cronk

Sorry. Try this:

=COUNTIFS([360 Photos Have Been Submitted]:[360 Photos Have Been Submitted], 0, [Permit Expiration Date]:[Permit Expiration Date], < DATE(2020, 7, 31), [Permit Expiration Date]:[Permit Expiration Date],

Mark

Mark Cronk

Hi @Justin Tackitt ,

You need to use a COUNTIFS function. The syntax is: COUNTIFS( range1, criterion1,[ range2, ​criterion2​... ])

Your formula would be:

=COUNTIFS([360 Photos Have Been Submitted]:[360 Photos Have Been Submitted], 1, [Permit Expiration Date]:[Permit Expriation Date], < 7/31/20, [Permit Expiration Date]:[Permit Expriation Date], >7/1/20)

Work?

Mark

Justin Tackitt

Thank you for your help!!!

I am getting an invalid operation error :(

=COUNTIFS([360 Photos Have Been Submitted]:[360 Photos Have Been Submitted], 0, [Permit Expiration Date]:[Permit Expiration Date], <7 / 31 / 20, [Permit Expiration Date]:[Permit Expiration Date], >7 / 1 / 20)

Mark Cronk

Sorry. Try this:

=COUNTIFS([360 Photos Have Been Submitted]:[360 Photos Have Been Submitted], 0, [Permit Expiration Date]:[Permit Expiration Date], < DATE(2020, 7, 31), [Permit Expiration Date]:[Permit Expiration Date],

Mark

Justin Tackitt

Oh don't be sorry, I'm thankful for the help!!

Your formula was missing the second date range so I added the following to the end of your formula:

>DATE(2020, 7, 1))

It returned a number, but not the correct number. It is counting all items between those date ranges, it's not taking into consideration whether or not the box is checked.

Justin Tackitt

I feel dumb now. I was counting the non checked boxes. It works now!

Thank you very much for your help!!!!

Very much appreciated!

Mark Cronk

On more try. Almost there.

=COUNTIFS([360 Photos Have Been Submitted]:[360 Photos Have Been Submitted], =1, [Permit Expiration Date]:[Permit Expiration Date], < DATE(2020, 7, 31), [Permit Expiration Date]:[Permit Expiration Date],>DATE(2020, 7, 1))

Mark

Mark Cronk

Perfect. Happy to help. Thanks for using the Community.

Be Well, Mark

Justin Tackitt

You saved the day for me!!! Thank you again, sir!