## Number of n's in a range

### Number of n's in a range

In the range a1:a3
123
221
332

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

### Number of n's in a range

This worked for me:

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

--

Dave Peterson
XXXX@XXXXX.COM

### Number of n's in a range

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

### Number of n's in a range

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

### Number of n's in a range

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

### Number of n's in a range

"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*")

### Number of n's in a range

The possibilities are almost limitless!

Biff

### Number of n's in a range

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

### Number of n's in a range

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

Yes, I screwed up.

SUMPRODUCT probably is best for this.

### Number of n's in a range

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?

### Number of n's in a range

It seems there can be no empty cells in the range for this to work....

### Number of n's in a range

"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'?

### Number of n's in a range

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.