Using a COUNTIFS formula if date criteria is met

mwiggins ✭✭✭✭
edited 12/09/19 in Smartsheet Basics

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).

Please help! I have been going at it this whole week to no avail!


  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

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


    =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.


  • mwiggins
    mwiggins ✭✭✭✭

    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!