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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭
    Answer ✓

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

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭
    Answer ✓

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

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages