+ Reply to Thread
Results 1 to 3 of 3

HELP - Formula to count for specific terms in an entire column except for specified cells

  1. #1
    Registered User
    Join Date
    08-13-2018
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    2

    Question HELP - Formula to count for specific terms in an entire column except for specified cells

    Hi all. First time posting.

    Apologies if this question has been asked, I did search for it, but came up with nothing. It's a bit specific, and maybe too out there(?).

    As the title says, I am looking for a fomula that will count an entire column for a specific term, but I want to exclude specific cells as well.

    The reasoning for this is I have an Excel file that I am regularly adding to, and thought there must be a formula out there that will count the entire column so I don't have to alter the range each time I update. I should also explain the specific term part. Without going into too much detail, I use 0, 1 and 2 to differenciate the entries I am adding. So my formula currently uses this to look for any "0" entries in a range:

    =COUNTIF(E13:E783,"0")

    So if the range was replaced with the entire E column I would be set... HOWEVER, I need to exclude E1 to E12 as rows 1 to 12 are a frozen (non-scrolling) part of my file. So I need that part excluded.

    Hopefully someone can help me!

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: HELP - Formula to count for specific terms in an entire column except for specified ce

    There are many ways, if you have given a simplified example then what works with that might not work in reality.

    =COUNTIF(E:E,0)-COUNTIF(E1:E12,0)

    =COUNTIF(E13:E1048576,0)

    =COUNTIF(E13:INDEX(E:E,MATCH(1e+100,E:E)),0)

    Try to get out of the habit of "0", numbers are not numbers when they are enclosed in double inverted commas. Many formulas will not work correctly when you use "0" instead of 0

  3. #3
    Registered User
    Join Date
    08-13-2018
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    2

    Re: HELP - Formula to count for specific terms in an entire column except for specified ce

    Thanks Jason! The first formula didn't work for me, but the second and third did. I've gone with the third.

    I think the reason the first one didn't work for me is I have this formula located in column E where it is also referring to itself. I can't see a way to change the entire layout to avoid this.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] highlight cells with VBA for specific (ish) terms
    By realrookie in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-07-2016, 08:39 AM
  2. Count terms in paragraphs in cells for certain dates
    By Speerdo in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-06-2015, 03:17 PM
  3. Alter existing formula to copy specific cells in row instead of copy entire column
    By painterartist in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-02-2014, 12:42 PM
  4. Seeking Formula to separate cells that contain specific terms
    By syncguy in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-19-2014, 03:19 AM
  5. [SOLVED] Formula to count number of blank cells for a specific condition in another column
    By Daniel_12 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-30-2013, 07:16 AM
  6. [SOLVED] Count Occurrences of specific text within cells in an entire worksheet?
    By audiofreak in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-30-2012, 12:34 PM
  7. Count specific cells in a column
    By martins in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-01-2008, 12:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1