Hello,

A1 A,B,X,C,X
A2 X,D,G,X
A3 F,G,K,L,X


I need to count how many X repeated at many cells using sumproduct function.

One way
In B1:
=SUMPRODUCT(--(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,"X",""))))
Adapt the range to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik
---
"A.Mourice" wrote:
A1 A,B,X,C,X
A2 X,D,G,X
A3 F,G,K,L,X


I need to count how many X repeated at many cells using sumproduct function.

To count the number of X's, use:

=LEN(A1&A2&A3)-LEN(SUBSTITUTE(A1&A2&A3,"X",""))

this will show 5 for your posted data.
--
Gary''s Student - gsnu200802


"A.Mourice" wrote:

Hello,

A1 A,B,X,C,X
A2 X,D,G,X
A3 F,G,K,L,X


I need to count how many X repeated at many cells using sumproduct function.

=SUMPRODUCT(LEN(A1:A3)-LEN(SUBSTITUTE(UPPER(A1:A3),"X","")))


"A.Mourice" wrote:

Hello,

A1 A,B,X,C,X
A2 X,D,G,X
A3 F,G,K,L,X


I need to count how many X repeated at many cells using sumproduct function.

I use the same equation at the following, but it didn't work

A1 1-, 2-, 3-,
A2 2-, 1-, 10-
A3 3-, 1-, 2-, 1-

I need to count how many 1- using sumproduct function

Thanks


"Gary''s Student" wrote:

To count the number of X's, use:

=LEN(A1&A2&A3)-LEN(SUBSTITUTE(A1&A2&A3,"X",""))

this will show 5 for your posted data.
--
Gary''s Student - gsnu200802


"A.Mourice" wrote:

Hello,

A1 A,B,X,C,X
A2 X,D,G,X
A3 F,G,K,L,X


I need to count how many X repeated at many cells using sumproduct function.

changing the formula:

=LEN(A1&A2&A3)-LEN(SUBSTITUTE(A1&A2&A3,"1-",""))

you should see 8
--
Gary''s Student - gsnu200802


"Gary''s Student" wrote:

To count the number of X's, use:

=LEN(A1&A2&A3)-LEN(SUBSTITUTE(A1&A2&A3,"X",""))

this will show 5 for your posted data.
--
Gary''s Student - gsnu200802


"A.Mourice" wrote:

Hello,

A1 A,B,X,C,X
A2 X,D,G,X
A3 F,G,K,L,X


I need to count how many X repeated at many cells using sumproduct function.