Ask Your Question

Revision history [back]

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.

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.

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.

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._$")

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

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

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

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:

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