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.
COUNTIFS and with 2 different values in same column?
Hi there,
I'm trying to create a formula to count when 2 different types of activity in a column ("Key Initiative" and "Initiative" have the value "Complete".
I've been trying different ways to organize COUNTIFS to solve the problem, but I'm getting either "0" or #Unparse as a result.
The most straightforward formula I've used is
=COUNTIFS(Type8:Type40, "Key Initiative", Type8:Type40, "Initiative", Status8:Status40, "Complete"
I get "0" as the answer.
Can anyone help me solve this?
Thanks!
Comments
-
COUNTIFS is a series of AND statements. You need an OR kind of thing:
This should work:
=COUNTIFS(Type8:Type40, "Key Initiative", Status8:Status40, "Complete") + =COUNTIFS(Type8:Type40, "Initiative", Status8:Status40, "Complete")
Craig
-
I always forget that simple trick! I need to remember the Countifs can be added together. Thanks for reminding me! I always try to come at these types of requests with a complicated formula. Sometimes simple math is the best solution!
-
I try to do the simple solutions first, and only get complicated if those don't work. I'm lazy in that way.
I try to keep in mind what the functions return value type is, which helps with the simple solutions.
Craig
-
Craig, I am using this but shwing "Invalid Operation:
=COUNTIF([Current Overall Status]:[Current Overall Status], "QA") + =COUNTIF([Current Overall Status]:[Current Overall Status], "UAT")
I am trying to count the total tasks that are in either QA or UAT.
HELP!
Thanks,
Rafsun
-
Get rid of the second = sign
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives