Countif formula

tmcgee

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

 

tmcgee

Screenshot uploaded

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

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