Countif formula

tmcgee ✭✭
edited 12/09/19 in Formulas and Functions


I have a sheet that tracks number of days we are over or under staffed with a -2, -1, 0, 1, 2

I would like to add a criterion that it only counts the days that are after today.

Here is the formula that was recommended by Smartsheets

=COUNTIFS($[U/O Monday]$6:$[U/O Friday]$58, -2, $[Week Of]$6:$[Week Of]$58, @cell > TODAY())


Here is my formula that says "Incorrect argument set"

=COUNTIFS($[U/O Monday]$6:$[U/O Friday]$58, -2, $[Week of]$6:$[Week of]$58, @cell > TODAY())




  • tmcgee
    tmcgee ✭✭

    Screenshot uploaded


  • tmcgee
    tmcgee ✭✭

    After further research I have added a checkbox to check the dates that are >Today so I can have it count the cells in the range that are "0" that also have the box checked.  I am now not getting an error, but it only wants to put a 0 or 1 now.


    =COUNTIFS($[U/O Monday]$7:$[U/O Friday]$59, "0", $Term$7:$Current$59, 1)

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

    The Checkbox column only accepts the following as values:

    0,1, false, true, no, yes, (these last four become 0,1,0,1 after entry)

    and any text.

    For example, it will accept "2" but not 2

    The issue with your original formula:

    =COUNTIFS($[U/O Monday]$6:$[U/O Friday]$58, -2, $[Week of]$6:$[Week of]$58, @cell > TODAY())

    is better described with different column and rows

    =COUNTIFS( [Col1]1: [Col5]10, -2, [Col6]1:[Col6]10, @cell > TODAY())

    the first range ( [Col1]1: [Col5]10 ) is 5 columns by 10 rows = 50 cells

    the second range ( [Col6]1:[Col6]10 ) is 1 column by 10 rows = 10 cells 

    The ranges need to be the same size.

    You could break this up into 5 calculations:

    =COUNTIFS( [Col1]1: [Col1]10, -2, [Col6]1:[Col6]10, @cell > TODAY()) +COUNTIFS( [Col2]1: [Col2]10, -2, [Col6]1:[Col6]10, @cell > TODAY())


    I hope that helps.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!