COUNTIFS using 2 colums
I am trying to return a count of how many active cardholders had their cards for 12 months. I'm using this formula and it keeps returning a value of 0.
=COUNTIFS([Cardmember Status]:[Cardmember Status], "ACTIVE ACCOUNT", Tenure:Tenure, "12")
I've tried using =12 and still returns 0. What am I missing?
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
Best Answer
-
Try removing the quotes from around the second 12.
=IF([Tenure Months]@row>12, 12)
and from inside of your COUNTIFS
=COUNTIFS([Cardmember Status]:[Cardmember Status], "ACTIVE ACCOUNT", Tenure:Tenure, 12)
Answers
-
Do you have any that match? Maybe it is returning the right answer?
-
What is in the Tenure column and how is that data populated?
-
The Tenure column is a formula. =IF([Tenure Months]@row>12, "12". I know there are at least a couple hundred 12's.
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
-
Any chance you could grab a screen shot? It doesn't look like there is anything wrong with the formula.
-
Try removing the quotes from around the second 12.
=IF([Tenure Months]@row>12, 12)
and from inside of your COUNTIFS
=COUNTIFS([Cardmember Status]:[Cardmember Status], "ACTIVE ACCOUNT", Tenure:Tenure, 12)
-
Yes I agree with @Paul Newcome however on testing a text/number column within a countifs you could declare criteria as =12 or "12" or 12 and they should all work!
-
That worked Paul! I removed both quotes and it returned a correct number. Thanks!
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
-
Well done!!
-
I do have an =12 in the formula. Didn't have it before. That may have been it all along! 😂
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
-
@Debbie Sawyer Yes. I agree it SHOULD work, but sometimes it doesn't (you know how Smartsheet can get sometimes). Since that is something very easy to change, it is one of the first things I give a try so that I can go ahead and rule it out if that isn't it.
-
@Darla Brown Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!