Variable Elimination
I am having trouble trying to eliminate a variable form my formula
=COUNTIFS([Current Status]1:[Current Status]80, NOT(CONTAINS("Early", @cell, "DNP", @cell)))
Best Answer
-
Oh, or do you mean contains GNG? or Completed, as well as the first criteria? Change the word to fit your conditions.
=COUNTIFS([GNG 1 Status]:[GNG 1 Status], CONTAINS("Completed"@cell), [Current Status]:[Current Status], AND(@cell <> "", NOT(CONTAINS("Early", @cell)), NOT(CONTAINS("DNP", @cell)))))
I apologize for not understanding. See if this is what you meant. If it doesn't work, please tell me what it is not doing
Answers
-
Hey @Shannon Lacerda
Try this
=COUNTIFS([Current Status]:[Current Status], AND(@cell<>"", NOT(CONTAINS("Early", @cell)), NOT(CONTAINS("DNP", @cell))))
cheers
-
@KDM thanks,
can you help me with this, I need to same parameter as the last formula to help count column.
=COUNTIFS([GNG 1 Status]1:[GNG 1 Status]80, ([Current Status]:[Current Status], AND(@cell <> "", NOT(CONTAINS("Early", @cell)), NOT(CONTAINS("DNP", @cell)))))
-
To clarify, we're replacing the [Current Status] column with the [GNG 1 Status] column, but keeping all of the same criteria? Also, I removed the row references in the range- unless you are specifically calling out that range of row1-row80 we can use the whole column reference instead.
=COUNTIFS([GNG 1 Status]:[GNG 1 Status], AND(@cell <> "", NOT(CONTAINS("Early", @cell)), NOT(CONTAINS("DNP", @cell)))))
If this isn't what you need, let me know what I misunderstood and we can tweak it.
-
I need to add up column (GNG Status) but not include the ones that have Early or DNP in the same row.
-
Did the above return the expected values?
-
Oh, or do you mean contains GNG? or Completed, as well as the first criteria? Change the word to fit your conditions.
=COUNTIFS([GNG 1 Status]:[GNG 1 Status], CONTAINS("Completed"@cell), [Current Status]:[Current Status], AND(@cell <> "", NOT(CONTAINS("Early", @cell)), NOT(CONTAINS("DNP", @cell)))))
I apologize for not understanding. See if this is what you meant. If it doesn't work, please tell me what it is not doing
-
@KDM Thanks that worked 👍️
-
@KDM so sorry to bother you again,
I have another one, this time I WANT to include the RFP, LOST, WON aspects of the Row, I dont get an error, but I just get a 0. which I know is wrong
=COUNTIFS([GNG 2 Status]:[GNG 2 Status], CONTAINS("Completed", @cell), [Current Status]:[Current Status], AND(@cell <> "", (CONTAINS("RFP", @cell)), (CONTAINS("LOST", @cell)), (CONTAINS("WON", @cell))))
-
Hey @Shannon Lacerda
Sorry I didn't see this post last night, and it's no bother.
Now we need to switch to an OR function.
=COUNTIFS([GNG 2 Status]:[GNG 2 Status], CONTAINS("Completed", @cell), [Current Status]:[Current Status], OR(CONTAINS("RFP", @cell), CONTAINS("LOST", @cell), CONTAINS("WON", @cell)))
Does this return the correct value?
cheers,
Kelly
-
@KDM Thank you that worked! :)
-
Always glad to help. Let me know if there's anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!