In the range a1:a3

123

221

332

How to count the number of 2's in the range,which would be 4

This worked for me:

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

--

Dave Peterson

XXXX@XXXXX.COM

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

--

Dave Peterson

XXXX@XXXXX.COM

Yes thanks,that works can you take it one step farther?

count the number of times 2 appears in the first postion =1

count the number of times 2 appears in the second postion =2

count the number of times 2 appears in the third postion =1

count the number of times 2 appears in the first postion =1

count the number of times 2 appears in the second postion =2

count the number of times 2 appears in the third postion =1

As long as the numbers are only 3 digits:

1st pos - =SUMPRODUCT(--(--LEFT(A1:A3,1)=2))

2nd pos - =SUMPRODUCT(--(--MID(A1:A3,2,1)=2))

3rd pos - =SUMPRODUCT(--(--RIGHT(A1:A3,1)=2))

Biff

=1

postion =2

postion =1

would be 4

1st pos - =SUMPRODUCT(--(--LEFT(A1:A3,1)=2))

2nd pos - =SUMPRODUCT(--(--MID(A1:A3,2,1)=2))

3rd pos - =SUMPRODUCT(--(--RIGHT(A1:A3,1)=2))

Biff

=1

postion =2

postion =1

would be 4

Use array formulas. If you haven't used these, enter the formulas below,

without the brackets, and press CTRL-SHIFT-ENTER to enter them as array

formulas -- Excel will supply the brackets.

count the number of times 2 appears in the first position :

{=SUM(IF(VALUE(LEFT(A1:A3,1))=2,1))}

count the number of times 2 appears in the second position:

{=SUM(IF(VALUE(MID(A1:A3,2,1))=2,1))}

count the number of times 2 appears in the third position:

{=SUM(IF(VALUE(MID(A1:A3,3,1))=2,1))}

Stan Scott

New York City

without the brackets, and press CTRL-SHIFT-ENTER to enter them as array

formulas -- Excel will supply the brackets.

count the number of times 2 appears in the first position :

{=SUM(IF(VALUE(LEFT(A1:A3,1))=2,1))}

count the number of times 2 appears in the second position:

{=SUM(IF(VALUE(MID(A1:A3,2,1))=2,1))}

count the number of times 2 appears in the third position:

{=SUM(IF(VALUE(MID(A1:A3,3,1))=2,1))}

Stan Scott

New York City

"Biff" < XXXX@XXXXX.COM > wrote...

Why assume these strings are only 3 digits?

Why use two sets of unary minuses?

1st pos - =SUMPRODUCT(--(MID(A1:A3,1,1)="2"))

2nd pos - =SUMPRODUCT(--(MID(A1:A3,2,1)="2"))

3rd pos - =SUMPRODUCT(--(MID(A1:A3,3,1)="2"))

Why use SUMPRODUCT?

1st pos - =SUMIF(A1:A3,"2*")

2nd pos - =SUMIF(A1:A3,"?2*")

3rd pos - =SUMIF(A1:A3,"??2*")

Why assume these strings are only 3 digits?

Why use two sets of unary minuses?

1st pos - =SUMPRODUCT(--(MID(A1:A3,1,1)="2"))

2nd pos - =SUMPRODUCT(--(MID(A1:A3,2,1)="2"))

3rd pos - =SUMPRODUCT(--(MID(A1:A3,3,1)="2"))

Why use SUMPRODUCT?

1st pos - =SUMIF(A1:A3,"2*")

2nd pos - =SUMIF(A1:A3,"?2*")

3rd pos - =SUMIF(A1:A3,"??2*")

Hi Harlan

why use SUMIF :-)

you probably meant

=COUNTIF(A1:A3,"2*")

....

Also if A1:A3 contains real numbers this formula returns '0' for me.

Only if the numbers are actually stored as 'Text' values this formula

works.

But the SUMPRODUXT formula works also in this cases. So SUMIF/COUNTIF

is not an option IMHO for this case

Frank

why use SUMIF :-)

you probably meant

=COUNTIF(A1:A3,"2*")

....

Also if A1:A3 contains real numbers this formula returns '0' for me.

Only if the numbers are actually stored as 'Text' values this formula

works.

But the SUMPRODUXT formula works also in this cases. So SUMIF/COUNTIF

is not an option IMHO for this case

Frank

"Frank Kabel" < XXXX@XXXXX.COM > wrote...

...

Yes, I screwed up.

SUMPRODUCT probably is best for this.

...

Yes, I screwed up.

SUMPRODUCT probably is best for this.

I am using numbers so Sumproduct works in this case but only if the

range is 50 cells or less.Any more and it bombs out.Any alternatives?

range is 50 cells or less.Any more and it bombs out.Any alternatives?

"Anderson" < XXXX@XXXXX.COM > wrote...

...

The SUMPRODUCT approach,

=SUMPRODUCT(--(MID(Range,p,1)="2"))

where p is 1, 2 or 3, should work for arbitrarily large single area ranges.

If I fill A1:J100 (1000 cells) with the formula

=INT(100+900*RAND())

and fill M1:V100 with the formulas

M1: =--(MID(A1,2,1)="2")

the formulas =SUMPRODUCT(--(MID(A1:J100,2,1)="2")) and =SUM(M1:V100)

return the same value. What's the *EXACT* formula you're trying to use? What

*PRECISELY* do you mean by 'it bombs out'?

...

The SUMPRODUCT approach,

=SUMPRODUCT(--(MID(Range,p,1)="2"))

where p is 1, 2 or 3, should work for arbitrarily large single area ranges.

If I fill A1:J100 (1000 cells) with the formula

=INT(100+900*RAND())

and fill M1:V100 with the formulas

M1: =--(MID(A1,2,1)="2")

the formulas =SUMPRODUCT(--(MID(A1:J100,2,1)="2")) and =SUM(M1:V100)

return the same value. What's the *EXACT* formula you're trying to use? What

*PRECISELY* do you mean by 'it bombs out'?

I was using the similar sumproduct you used,my problem was using the

whole column for the range. ...A:A

I seem to remember now array formula and sumproduct do not work if you

use the whole column as the range,it was easily enough modified.

whole column for the range. ...A:A

I seem to remember now array formula and sumproduct do not work if you

use the whole column as the range,it was easily enough modified.

1. Conditional count in range w/#N?A's

3. convert range of numbers in one record into range of single-number records

4. Finding the Value (of text and numbers) between N/A's

5. regexp for reg language that has an even number of a's and b's

6. javascript links don't work on ns/linux, but do on ns/win

7. loadMovie issue- mac ns 4.7, win ns 6.1

8. Create Primary NS and Secondary NS

10. fms ns.play(file, -2) Vs ns.play(stream, -2)

11. memory 70 ns in mothercard at 80 ns

12. Problem with NS 7 - Layout works in IE6, Opera 7 and NS 6

13. [PATCH -mm 8/8] user ns: implement user ns unshare

14. Ultra 5 - Combining 50 ns & 60 ns RAM

15. [PATCH 8/8] user ns: implement user ns unshare

13 post • Page:**1** of **1**