Count cells that contain either a or b

If you want to count cells, normally you would use COUNT or COUNTIFS functions. However, since you are counting cells with either value, they will double count the cells which have both. Hence, this approach helps you eliminate that issue.

There are 2 ways of counting cells having either specific values. One of them is to count in 1 cell with a complex syntax and the other with a simpler syntax but it requires an additional column.

Count cells containing a or b in 1 cell

Formula =SUMPRODUCT(--((ISNUMBER(FIND("a", range)) +  ISNUMBER(FIND("b", range)))>0))

Explanation

Overall, the idea is to count the cells containing each value, to evaluate the cells containing one and those containing both as the same and to sum them up. First, a combination of FIND and ISNUMBER counts the cells, note that the cells containing both are double counted. Next, a TRUE/FALSE function (>0) separates the cells into 2 types, containing either values and not containing any; then the double negative (–) converts TRUE/FALSE value into 1/0. Finally, SUMPRODUCT adds them up together.

Example 1

Want to count cells with either a or b with only 1 cell, you can do as follow

  1.     Select H4
  2.     Type (ISNUMBER(FIND("a", C5:C10))+(ISNUMBER(FIND("b",C5:C10))))
  3.     Insert function >0
  4.     Cover the whole line with a double negative (–)
  5.     Make it an argument of SUMPRODUCT()

Figure 1.Counting cells containing a or b with 1 cell

FIND(“a”, C5:C10) function captures how many times “a” appears in C5:C10 and outputs an array constant. ISNUMBER consumes that array and returns a TRUE/FALSE array constant for “a”. Similarly, there is a TRUE/FALSE array constant for “b”, and they are now added together. The result is another array constant which, in this example, looks like {2; 1; 0; 0; 1; 1} and each row tells how many times “a” or “b” appears.

Notice that there are 2 types of cells: ones that contain neither “a” nor “b” which have value 0 and ones that contain either “a” or “b” which have a value greater than 0. This is the main idea of the approach, to insert a TRUE/FALSE function (>0) to create an array of TRUE and FALSE, which are converted into 1 and 0 by double negative (–). Lastly, SUMPRODUCT adds up the values. (Note that SUM does not work with arrays).

Count cells containing a or b with an additional column

Formula =--(SUM(COUNTIF(cell,{"*a*","*b*"}))>0)

Explanation

If your contained value set is small, in this case, it has only 2 components “a” and “b”, you can use COUNTIF and evaluate with a helping column. First, you need to find the number of “a” and “b” inside 1 cell with COUNTIF. After summing up the count, use the similar idea above and decide whether the cell has either “a” or “b”, or not. Lastly, convert them to 1/0 and sum up the additional column.

Example 2

To count cells containing either a or b with an additional column, you should do as follow

  1.     Add a column right next to your text column
  2.     Select the top cell, D5, of the column and insert COUNTIF(C5, {“*a*”,”*b*”})
  3.     Cover the function with SUM and >0
  4.     Cover the function with double negative (–) and add = before it
  5.     Copy the syntax to the rest of the column

Figure 2.Count cells using an additional column

From the inside out, {“*a*”,”*b*”} is an array value input to COUNTIF to find how many times each letter appears respectively in C5. Note that the * represents the wildcard value, a value that can replace any other value, so *a* means any text that contains a. The result is an array and is summed up to get a number value. Like the 1-cell method, this number is either 0 for non-appearance of both letters or greater than 0 for at least 1 letter appears. It is then categorized to TRUE/FALSE by >0 functions and converted to 1/0 by double negative (–).

Now the result will be 1 if containing either a or b, or 0 if not containing either. You can SUM the column easily to get the count of the whole column.      

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar