Developer Forum

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

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

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

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

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

The possibilities are almost limitless!

Biff

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

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

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?

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

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

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.