# Using a COUNTIFS formula if date criteria is met

Options
✭✭✭✭
edited 12/09/19

Hello smartsheet community,

I am having some serious difficulty getting a COUNTIFS formula to work based on if a row in another sheet meets a data criteria.

My COUNTIFS formula works right now when referencing another sheet, but I would like to use the same formula and add onto it another function which will make it more accurate.

What I'd like to have is a formula which looks at another sheet and first looks at the DATE column which all have dates entered - the formula looks only at the rows with a date in the date column that is between X and Y dates - Then of those rows which have a date in the date column between X and Y dates - a COUNTIFS formula is performed on only those rows (I already have the COUNTIFS formula).

• ✭✭✭✭✭✭
Options

=COUNTIFS({date range},> DATE(YY,MM,DD),{date range},< DATE(YY,MM,DD),etc...)

or

=COUNTIFS({date range},AND(> DATE(YY,MM,DD),< DATE(YY,MM,DD)),etc...)

Both should work.

For example

=COUNTIFS({PS | D}, >DATE(2018, 6, 12), {PS | D}, <DATE(2018, 6, 30))

>= and <= can also be used.

Craig

• ✭✭✭✭
Options

Thank you Craig,

Fortunately I was able to get it on my own using the DATE function in a more pronounced way as well as adding multiple COUNTIFS in the same link.

I will experiment with the use of AND that you showed, however, to see if there is still yet a more optimal method to doing this.

Thank you for the reply and help!