0

I have a table and I want to count the number of times a string appears on a column. I used COUNTIF to do this, but I want to exclude in the count those cells with strings ending in any number.

I tried using this formula but the criteria is unrecognized as it always erroneously gives a value of 0:

=COUNTIF([Name],"*"&A3&NOT(ISNUMBER(VALUE(RIGHT("*")))))

EDIT:

The screenshot below shows I want to count how many times Area 25 appears on column Name,
including San Jose Area 25 (since it contains the substring Area 25)
but NOT San Jose Area 252 and Area 251 (since these strings end in numbers AFTER the string Area 25), and NOT Area 25-A either (since Area 25 is connected with another substring -A:

Excel COUNTIF error

So the result I want to see in Cell D3 is 4, which are:
Area 25 (cells A3 and A11),
Area 25 Zone B (cell A4), and
San Jose Area 25 (cell A8).

Improve this question
7
  • Something like this should work: =SUMPRODUCT(1*(ISERROR(VALUE(RIGHT(G1:G10))))). I wasn't able to test this with a named table/column, though
    – Bandersnatch
    Commented Oct 14, 2017 at 16:51
  • @Bandersnatch Thanks for the response, but what I am trying to achieve is to count how many times a string (like "Area 25") occurs in a column.
    – JAT86
    Commented Oct 15, 2017 at 1:26
  • Yes, that's what this formula does, except I thought you wanted to exclude cells that end with a number - like Area 25.
    – Bandersnatch
    Commented Oct 15, 2017 at 4:44
  • @Bandersnatch Sorry for being confusing. I have added a screenshot and added some details. Please check. Thank you.
    – JAT86
    Commented Oct 15, 2017 at 21:55
  • 1
    If it can be assumed that what you are looking for ends with "Area 25" or has "Area 25"+space+additional, then try this: =SUM(COUNTIFS(A21:A11,{"*Area 25","*Area 25 *"})) If you are not familiar an array can be used to capture note all of the alternatives, then the sum is counting each answer from the array.
    – gns100
    Commented Oct 16, 2017 at 23:00

1 Answer 1

Reset to default
0

You can approach the "IF" part differently.

=COUNT(SEARCH("Area 25",IF(NOT(ISNUMBER(VALUE(RIGHT(A1:A10,1)))),A1:A10,"")))

RIGHT(something,1) finds the last character of every STRING, then VALUE() tries to make it a number. ISNUMBER() tests the success of that, and NOT() reverses the TRUE/FALSE values found by it to be FALSE/TRUE.

IF() then uses those TRUE/FALSE results to decide how to branch, either returning the cell value or "". You are testing only those cells that met the test for not having a numeric digit as their last character.

SEARCH() looks through all of that, blanks and all, but importantly, those cells that still have values, for the content you are seeking. It will return the starting points of said string in each, so one can't simply SUM() the results. The error returns wouldn't make that easy either. However, the successes ARE numeric results, so a simple COUNT() finishes things off by counting them. It ignores anything not a number so it ignores the errors. It also counts a number as "1, each" so if the target string begins at character 37, hence a return of 37 for that cell, it still COUNT()'s simply as "1", not "37" so does what you want here.

At the time of the question, and now (2021), the range can be a Named Range allowing it to expand and contract without needing changed in the formula.

Now, if one WANTS to definitively see the range and be sure it's right without delving into the Named Ranges, one can use LET() to define the "named range" at the very beginning of the formula so it is easy to see and edit.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .