Countif formula
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())
Comments
-
Screenshot uploaded
-
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)
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!