CODEWITHSUNDEEP
excel
Emily Sechrist
Emily Sechrist

Reputation: 11

Excel COUNTIF Function Conditional Formatting

I have a dataset where I have to find the number of times the letters "age" appear while excluding the actual word "age".

If I use the countif function, I get all words that contain "age", but not sure how to exclude the actual word "age" from that count. I have tried to limit the word length, for example only count words that are greater than 3 characters and contain "age", but have been unsuccessful.

I appreciate all of the suggestions. Thanks!

Upvotes: 1

Views: 422

Answers (2)

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5185

A bit more exhaustive, and requires a helper column, but could be what you're looking for:

I have this array formula in cell B1, searching for instances of age in cell A1, excluding the stand-alone word age.

= SUMPRODUCT((MMULT((ABS(ABS(IFERROR(CODE(MID(A1,MODE.MULT(IF((MID(A1,
  ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(A1)-2)),3)="age"),ROW(INDEX($A:$A,1):
  INDEX($A:$A,LEN(A1)-2))*{1,1}))+{-1,3},1)),32)-93.5)-16)<13)+0,{1;1})>0)+0)

Note this is an array formula, so you must press Ctrl+Shift+Enter instead of just pressing Enter after typing the formula.

And then you can just use a simple SUM at the bottom of the column to sum up all of the instances.

The formula essentially finds each instance of age in the string, looks at the character immediately before and after it, and if either of these characters are a letter (capital or lowercase), then a match is found. The formula counts the matches.

See below for working example. I came up with several examples to demonstrate how it works.

enter image description here

Upvotes: 0

Lee Mac
Lee Mac

Reputation: 16015

Assuming your data inhabits column A, you could use the COUNTIFS function in the following way:

=COUNTIFS(A:A,"*age*",A:A,"<>age")

EDIT:

Following clarification that cells contain multiple words, please try the following:

=COUNTIFS(A:A,"*age*",A:A,"<>age",A:A,"<>* age",A:A,"<>age *",A:A,"<>* age *")

Upvotes: 2

Related Questions