CountIF for whole column Not just a range of rows
I'm trying to count the numbers of "text a" appears in an entire column.
This is the formulas I'm working with, that both produces an unparseable error are:
=COUNTIF(contains("Resident Instruction", [Resident or World Campus Offering]))
=COUNTIF([Resident or World Campus Offering], ="Resident Instruction")
What am I doing wrong?
Best Answer
-
When referencing an entire column in the same sheet, the format is [Column Name]:[Column Name].
How you create your formula for this count depends on your data. If the value you are looking for is the entire cell value (i.e. it's a text/number or single select drop down that equals "Resident Instruction") then you could just use a simple COUNTIF:
=COUNTIF([Resident or World Campus Offering]:[Resident or World Campus Offering], "Resident Instruction")
If you are trying to count the number of occurrences of this text in a multi-select column, you would need to use a COUNTIF with HAS:
=COUNTIF([Resident or World Campus Offering]:[Resident or World Campus Offering], HAS(@cell, "Resident Instruction"))
If you are trying to count the number of occurrences of this text in a text/number column where it could be mixed with other text, you would want to use COUNTIF with CONTAINS:
=COUNTIF([Resident or World Campus Offering]:[Resident or World Campus Offering], CONTAINS("Resident Instruction", @cell))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
When referencing an entire column in the same sheet, the format is [Column Name]:[Column Name].
How you create your formula for this count depends on your data. If the value you are looking for is the entire cell value (i.e. it's a text/number or single select drop down that equals "Resident Instruction") then you could just use a simple COUNTIF:
=COUNTIF([Resident or World Campus Offering]:[Resident or World Campus Offering], "Resident Instruction")
If you are trying to count the number of occurrences of this text in a multi-select column, you would need to use a COUNTIF with HAS:
=COUNTIF([Resident or World Campus Offering]:[Resident or World Campus Offering], HAS(@cell, "Resident Instruction"))
If you are trying to count the number of occurrences of this text in a text/number column where it could be mixed with other text, you would want to use COUNTIF with CONTAINS:
=COUNTIF([Resident or World Campus Offering]:[Resident or World Campus Offering], CONTAINS("Resident Instruction", @cell))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
COUNTIF worked perfectly for what I was trying to accomplish, thanks!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.1K Get Help
- 414 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
