CountIfs Formula
I would like the below formula to look at the Associate column (external sheet) and find Ashley. If it is Ashely then I want it to count if there is an issue logged in a four column range (same external sheet). It is comparing the issue in the range to the cell in my primary sheet.
=COUNTIFS({Associate}, "Ashley", {Issue Range}, CONTAINS([Primary Column]@row, @cell))
Best Answer
-
All you need to do is remove some of the closing parenthesis from the very end of the formula and use them to close out each of the individual COUNTIFS.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Hey @Joey135
I may be wrong but you may have been over thinking what's needed to accomplish what your wanting.
Try this.
=Countifs({Associate},"Ashley",{Issue Range},[Primary Column]@row)
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
@Mark.poole I had tried a version of that but got an unparseable error. Any other suggestions?
=COUNTIFS({Associate}, "Ashley", {Issue Range}, [Primary Column]@row, @cell))
-
Could I perhaps have an example of the sheet your working with so I could try building you a solution?
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Does the count if work with out looking for Ashley?
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
This works: =COUNTIF({Issue Range}, CONTAINS([Primary Column]@row, @cell))
But if I add any distinguishing factors, it does not.
-
Screen Shot
-
@Joey135 You forgot the CONTAINS function in the second formula.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome The formula gives me and Incorrect Argument error. I feel like this should be an easy one, but I guess not. Do you think is make any difference that the Associate column I am referring to is a contact list. Maybe I should try the email address.
=COUNTIFS({Associate}, "Ashley", {Issue Range}, CONTAINS([Primary Column]@row, @cell))
-
Just to reiterate:
Working formula: =COUNTIF({Issues Range 1-4}, CONTAINS([Primary Column]3, @cell))
Not Working formula: =COUNTIFS({Associate}, "Ashley", {Issues Range 1-4}, CONTAINS([Primary Column]1, @cell))
I seem to have stumped the community. I really appreciate this forum, it has never failed to get me the answer I need. I am in a holding period until I get this to work so if there is anyone out there that has a suggestion, I am open and waiting.
-
How many columns does {Issues Range 1-4} cover?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Four columns. Maybe I need to break them out some how.
-
That's where the new error is coming from. All ranges inside of a function must be of the same size and shape. If your contact range is a single column, then all other ranges in the COUNTIFS must be single columns. You will need to write out 4 separate COUNTIFS (one for each of the columns) and then add them together.
=COUNTIFS(………………..) + COUNTIFS(………………..) + COUNTIFS(……………..) + COUNTIFS(……………………….)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
This is what I understood:
=COUNTIFS({Associate}, "Ashley", COUNTIFS({Issue #1} + COUNTIFS({Issue #2} + COUNTIFS({Issue #3} + COUNTIFS({Issue #4}, CONTAINS([Primary Column]@row, @cell))))))
Incorrect Argument Set
-
Your parenthesis are off. Your syntax for adding them together would be the same as in my example. You just need to fill in the middle of each with your range/criteria sets.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Do you mean the below? This returns a 0 when I know there are issues to count.
=COUNTIFS({Associate}, "Ashley", {Issue #1}, CONTAINS([Primary Column]@row, @cell) + COUNTIFS({Associate}, "Ashley", {Issue #2}, CONTAINS([Primary Column]@row, @cell) + COUNTIFS({Associate}, "Ashley", {Issue #3}, CONTAINS([Primary Column]@row, @cell) + COUNTIFS({Associate}, "Ashley", {Issue #4}, CONTAINS([Primary Column]@row, @cell)))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!