Number of n's in a range

[Anderson]

In the range a1:a3
123
221
332

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

 

[Dave Peterson]

This worked for me:

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

 

[Anderson]

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

 

[Biff]

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

 

[Stan Scott]

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

 

[Harlan Grove]

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

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

 

[Biff]

The possibilities are almost limitless!

Biff

-----Original Message-----


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


.

 

[Frank Kabel]

Hi Harlan

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

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

 

[Harlan Grove]

why use SUMIF
you probably meant
=COUNTIF(A1:A3,"2*")

Yes, I screwed up.

SUMPRODUCT probably is best for this.

 

[Anderson]

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?

 

[Anderson]

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

 

[Harlan Grove]

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

....

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

 

[Anderson]

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.