sumproduct text
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 text
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. |
sumproduct text
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 text
=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. |
sumproduct text
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. |
sumproduct text
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. |
All times are GMT +1. The time now is 02:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com