TODAY Function with COUNTIFS



I have a formula just now which is :

=COUNTIFS({Business Case}, $Measure5, {Business Unit}, [Brand X]$3, {PROJECT STATUS}, OR(@cell = "Shipped", @cell = "In Progress"))

This is count the number on 'on time' and 'late business case's where business unit is brand X where the project status is either 'shipped' or 'in progress'.

I also wanted to add the formula to include a parameter of looking at a date column, so it includes in the count from TODAY up to 12 months from that date [-365].

I found for the TODAY function the formula is :

=COUNTIF([Date Column]:[Date Column], @cell> TODAY(-365))

I'm trying to incorporate the TODAY function into my existing formula which works, but failing - any help?

=COUNTIFS({Business Case}, $Measure5, {Business Unit}, [Brand X]$3, {PROJECT STATUS}, OR(@cell = "Shipped", @cell = "In Progress"), [BC Date]:[BC Date], @cell> TODAY(-365))

Many thanks.


Best Answer


  • L_123
    L_123 ✭✭✭✭✭✭

    It is failing because the number of cells in each reference in your other sheet references are different than that of the same sheet reference BC Date.

  • Lindsay Wilson

    @L@123 thanks for looking at my query!

    Not too sure I understand what you mean. What you suggest the below?

    =COUNTIFS({Business Case}, $Measure5, {Business Unit}, [Brand X]$3, {PROJECT STATUS}, OR(@cell = "Shipped", @cell = "In Progress"), [BC Date], @cell> TODAY(-365))


  • Lindsay Wilson

    @L@123 the second worked! thank you so much spent days looking at this!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!