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:



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

  • 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 Oct 14 '17 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 Oct 15 '17 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 Oct 15 '17 at 4:44
  • @Bandersnatch Sorry for being confusing. I have added a screenshot and added some details. Please check. Thank you. – JAT86 Oct 15 '17 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 Oct 16 '17 at 23:00

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.