Avoiding counting the same cell twice
 

Please help, I'm stuck.

How can I count the number of cells containing certain words but avoid
counting the same cell twice.

E.g.

Column A
A
B
C A
B A
A B
B
A

How do I count the number of cells containing A added to the number of cells
that contain B without double counting those that contain A and B

The answer in the above example should be 6 (NOT 7)

Many thanks

Avoiding counting the same cell twice
 

=SUMPRODUCT(--(LEFT(A1:A7,1)={"A","B"}))

---------------
mama no teeth

"JRD" wrote:

Please help, I'm stuck.

How can I count the number of cells containing certain words but avoid
counting the same cell twice.

E.g.

Column A
A
B
C A
B A
A B
B
A

How do I count the number of cells containing A added to the number of cells
that contain B without double counting those that contain A and B

The answer in the above example should be 6 (NOT 7)

Many thanks

Avoiding counting the same cell twice
 

One way...

If you download and install the latest version of the morefunc.xll
add-in, you can invoke:

=SUM((REGEX.SUBSTITUTE(UPPER(A2:A8),"[ AB]","")="")+0)-COUNTBLANK(A2:A8)

which must be confirmed with control+shift+enter, not just with enter.

JRD wrote:
Please help, I'm stuck.

How can I count the number of cells containing certain words but avoid
counting the same cell twice.

E.g.

Column A
A
B
C A
B A
A B
B
A

How do I count the number of cells containing A added to the number of cells
that contain B without double counting those that contain A and B

The answer in the above example should be 6 (NOT 7)

Many thanks