First time here? Check out the FAQ!

1 | initial version |

E.g., your reference values `AA-, AB-, AC-, AD-, DA_, DB_, DC_, DD_`

are in D1-D8. Then, formula

```
=SUMPRODUCT(NOT(ISNA(VLOOKUP(A1:A7;$D$1:$D$8;1;0)));NOT(ISNA(VLOOKUP(B1:B7;$D$1:$D$8;1;0))))
```

does what you need.

2 | No.2 Revision |

E.g., your reference values `AA-, AB-, AC-, AD-, DA_, DB_, DC_, DD_`

are in D1-D8. Then, formula

```
=SUMPRODUCT(NOT(ISNA(VLOOKUP(A1:A7;$D$1:$D$8;1;0)));NOT(ISNA(VLOOKUP(B1:B7;$D$1:$D$8;1;0))))
```

does what you need.

If you enable regular expressions in formulas, then

```
=SUMPRODUCT(ISNUMBER(SEARCH("^[AD].[-_]$";A1:A7));ISNUMBER(SEARCH("^[AD].[-_]$";B1:B7)))
```

will also work.

3 | No.3 Revision |

E.g., your reference values `AA-, AB-, AC-, AD-, DA_, DB_, DC_, DD_`

are in D1-D8. Then, formula

```
=SUMPRODUCT(NOT(ISNA(VLOOKUP(A1:A7;$D$1:$D$8;1;0)));NOT(ISNA(VLOOKUP(B1:B7;$D$1:$D$8;1;0))))
```

does what you need.

If you enable regular expressions in formulas, then

~~=SUMPRODUCT(ISNUMBER(SEARCH("^[AD].[-_]$";A1:A7));ISNUMBER(SEARCH("^[AD].[-_]$";B1:B7)))
~~=SUMPRODUCT(ISNUMBER(SEARCH("^A.-|D._$";A1:A7));ISNUMBER(SEARCH("^A.-|D._$";B1:B7)))

will also work.

4 | No.4 Revision |

E.g., your reference values `AA-, AB-, AC-, AD-, DA_, DB_, DC_, DD_`

are in D1-D8. Then, formula

```
=SUMPRODUCT(NOT(ISNA(VLOOKUP(A1:A7;$D$1:$D$8;1;0)));NOT(ISNA(VLOOKUP(B1:B7;$D$1:$D$8;1;0))))
```

does what you need.

If you enable regular expressions in formulas, then

```
=SUMPRODUCT(ISNUMBER(SEARCH("^A.-|D._$";A1:A7));ISNUMBER(SEARCH("^A.-|D._$";B1:B7)))
```

will also ~~work.~~work. As this:

```
=COUNTIFS(A1:A7;"^A.-|D._$";B1:B7;"^A.-|D._$")
```

5 | No.5 Revision |

E.g., your reference values `AA-, AB-, AC-, AD-, DA_, DB_, DC_, DD_`

are in D1-D8. Then, formula

```
=SUMPRODUCT(NOT(ISNA(VLOOKUP(A1:A7;$D$1:$D$8;1;0)));NOT(ISNA(VLOOKUP(B1:B7;$D$1:$D$8;1;0))))
```

does what you need.

If you enable regular expressions in formulas, then

```
=SUMPRODUCT(ISNUMBER(SEARCH("^A.-|D._$";A1:A7));ISNUMBER(SEARCH("^A.-|D._$";B1:B7)))
```

will also work. As this:

```
=COUNTIFS(A1:A7;"^A.-|D._$";B1:B7;"^A.-|D._$")
```

See here the full list of regular expressions in ICU (used in LO).

6 | No.6 Revision |

E.g., your reference values `AA-, AB-, AC-, AD-, DA_, DB_, DC_, DD_`

are in D1-D8. Then, formula

```
=SUMPRODUCT(NOT(ISNA(VLOOKUP(A1:A7;$D$1:$D$8;1;0)));NOT(ISNA(VLOOKUP(B1:B7;$D$1:$D$8;1;0))))
```

does what you need.

If you enable regular expressions in formulas, then

~~=SUMPRODUCT(ISNUMBER(SEARCH("^A.-|D._$";A1:A7));ISNUMBER(SEARCH("^A.-|D._$";B1:B7)))
~~=SUMPRODUCT(ISNUMBER(SEARCH("^(A.-|D._)$";A1:A7));ISNUMBER(SEARCH("^(A.-|D._)$";B1:B7)))

will also work. As well as this:

~~=COUNTIFS(A1:A7;"^A.-|D._$";B1:B7;"^A.-|D._$")
~~=COUNTIFS(A1:A7;"A.-|D._";B1:B7;"A.-|D._")

See here the full list of regular expressions in ICU (used in LO).

7 | No.7 Revision |

E.g., your reference values `AA-, AB-, AC-, AD-, DA_, DB_, DC_, DD_`

are in D1-D8. Then, formula

```
=SUMPRODUCT(NOT(ISNA(VLOOKUP(A1:A7;$D$1:$D$8;1;0)));NOT(ISNA(VLOOKUP(B1:B7;$D$1:$D$8;1;0))))
```

does what you need.

If you enable regular expressions in formulas, then

```
=SUMPRODUCT(ISNUMBER(SEARCH("^(A.-|D._)$";A1:A7));ISNUMBER(SEARCH("^(A.-|D._)$";B1:B7)))
```

will also work. As well as this:

```
=COUNTIFS(A1:A7;"A.-|D._";B1:B7;"A.-|D._")
```

The explanation why the `[A?-|D?_]`

expression is wrong:
1. The [] brackets in regex define a **set** - i.e., "in this place any of the following is allowed". So, in your example (`=COUNTIFS(A1:A7, "[A?-|D?_]", B1:B7, "[A?-|D?_]")`

), you looked for cells containing one-char values in the set defined in the square brackets.
1.1. Aside: the expression in square brackets has special treatment of `-`

, as @pierre-yves samyn noted: it means range. So, your charset included A, *all chars* between ? and | inclusive, D, and _.
2. The intended usage of ? is also wrong. You implied it to mean "any character in this position; mandatory present". If used in regexes (outside of sets), the meaning of unescaped `?`

is to mean "zero or one of previous expression".

See here the full list of regular expressions in ICU (used in LO).

8 | No.8 Revision |

E.g., your reference values `AA-, AB-, AC-, AD-, DA_, DB_, DC_, DD_`

are in D1-D8. Then, formula

```
=SUMPRODUCT(NOT(ISNA(VLOOKUP(A1:A7;$D$1:$D$8;1;0)));NOT(ISNA(VLOOKUP(B1:B7;$D$1:$D$8;1;0))))
```

does what you need.

If you enable regular expressions in formulas, then

```
=SUMPRODUCT(ISNUMBER(SEARCH("^(A.-|D._)$";A1:A7));ISNUMBER(SEARCH("^(A.-|D._)$";B1:B7)))
```

will also work. As well as this:

```
=COUNTIFS(A1:A7;"A.-|D._";B1:B7;"A.-|D._")
```

The explanation why the `[A?-|D?_]`

expression is ~~wrong:
1. ~~wrong:

- The [] brackets in regex define a
**set**- i.e., "in this place any of the following is allowed". So, in your example (`=COUNTIFS(A1:A7, "[A?-|D?_]", B1:B7, "[A?-|D?_]")`

), you looked for cells containing one-char values in the set defined in the square~~brackets. 1.1.~~brackets. Aside: the expression in square brackets has special treatment of`-`

, as @pierre-yves samyn noted: it means range. So, your charset included A,*all chars*between ? and | inclusive, D, and~~_. 2.~~_. - The intended usage of ? is also wrong. You implied it to mean "any character in this position; mandatory present". If used in regexes (outside of sets), the meaning of unescaped
`?`

is to mean "zero or one of previous expression".

See here the full list of regular expressions in ICU (used in LO).

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.