Using COUNTIF and SUMIF with dates

David Johnson
David Johnson ✭✭
edited 12/09/19 in Formulas and Functions

Hi everyone,

I need help understanding how I can use dates in COUNTIF and SUMIF formulas.  I have all other parts of the formula working but as soon as I try and add a date condition at all it fails.  What I need is something that makes it include all rows where the date in the column Date Closed is the current month.

My current formula looks like: =COUNTIFS({Bus Unit}, "QUARRIES VIC METRO", {RFI STATUS}, "CLOSED", {DATE CLOSED}, "MISSING BIT")

Can anyone help me with what goes in the missing bit?

Thanks

Tags:

Comments

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    This is a piece of a formula I've used to identify a specific month for conditional formulas: 

    [Date Range], IFERROR(MONTH(@cell), 0) = 1

    Where the = 1 is looking for January. If the Cell is blank, it'll just move on and not toss you an error. 

    Without testing, your formula would look something like this: 

    =COUNTIFS({Bus Unit}, "QUARRIES VIC METRO", {RFI STATUS}, "CLOSED", {DATE CLOSED}, IFERROR(MONTH(@cell), 0 =1)

     

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If you need a specific date, you would simply use a DATE(yyyy,mm,dd) in place of the MONTH function shown above. The same thing can be said for any other date related functions such as YEAR, WEEKNUMBER, YEARDAY, WEEKDAY, DAY, etc...

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!