Countif formula

Options
✭✭
edited 12/09/19

Hello

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

Tags:

• ✭✭
Options

• ✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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

etc...

I hope that helps.

Craig

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!