Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Nesting COUNTIF/AND Formula
I am trying to create a formula that returns the count of items past due, 0-30 days, 31-60 days, and greater than 61 days.
The formulas I'm using is (respectively):
=COUNTIF(AND([Past Due]2:[Past Due]18, "True", [# of Days Past Due]2:[# of Days Past Due]18, <31 >= 0))
=COUNTIF(AND([Past Due]2:[Past Due]18, "True", [# of Days Past Due]2:[# of Days Past Due]18, >=31 <= 60))
=COUNTIF(AND([Past Due]2:[Past Due]18, "True", [# of Days Past Due]2:[# of Days Past Due]18, >=61))
however, I keep getting the following error "INCORRECT ARGUMENT SET".
Comments
-
Ray,
Is the [Past Due] column a check box or text?
Why do you need a [Past Due] column if you are determining how many days past due it is?
I would write the 0-30 day formula like this:
=COUNTIFS([# of Days Past Due]2:[# of Days Past Due]18, >=0, [# of Days Past Due]2:[# of Days Past Due]18, <31)
The COUNTIFS function acts the same as
COUNTIF(AND(expression1, criteria1, expression2, criteria2))
For >-61, you can revert to COUNTIF because there is only the one criteria.
Hope this helps.
Craig
-
Hi Craig,
Yes, the [Past Due] column is a check box. I also have a column that counts the [# of Days Past Due]. I have both of these because I have some conditional formatting running in the background and thought I could leverage the information to run formulas as well.
The =COUNTIFS function you provided worked out. Thanks!
-
Ray,
I thought that might be the case.
As you can see, it was not needed for the functionality you asked for.
In the future, be aware the Check Box columns are Boolean.
these will all get the same results. I prefer the first one.
=IF(ChkBox23, "I am true", "I am false")
=IF(ChkBox23 = 1, "I am true", "I am false")
=IF(ChkBox23 = true, "I am true", "I am false")
=IF(ChkBox23 = "true", "I am true", "I am false")
=IF(ChkBox23 = "True", "I am true", "I am false")
I was surprised the last two work, but they do.
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives