CountIF for whole column Not just a range of rows

Alison Laninger · 05/23/22

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?

Jeff Reisman · 05/23/22

@Alison Laninger ,

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))

Jeff Reisman · 05/23/22

@Alison Laninger ,

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))

maxmpma · 06/21/24

Jeff Reisman

May 23, 2022

@Alison Laninger ,
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))

COUNTIF worked perfectly for what I was trying to accomplish, thanks!!!

Formulas and Functions

Formulas and Functions

CountIF for whole column Not just a range of rows

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Finding if a date falls in a range in a referenced sheet

CountIF contains name from a contact list help needed

Formula for averaging days