Number of n's in a range

Number of n's in a range

Post by Anderso » Sun, 25 Jul 2004 11:00:00


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

Post by Dave Peter » Sun, 25 Jul 2004 11:13:25

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

Post by Anderso » Sun, 25 Jul 2004 11:41:17

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

Post by Biff » Sun, 25 Jul 2004 12:20:04

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

Post by Stan Scot » Sun, 25 Jul 2004 12:57:54

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

Post by Harlan Gro » Sun, 25 Jul 2004 12:59:25

"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

Post by Biff » Sun, 25 Jul 2004 13:16:59

The possibilities are almost limitless!

Biff
 
 
 

Number of n's in a range

Post by Frank Kabe » Sun, 25 Jul 2004 13:41:22

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

Post by Harlan Gro » Sun, 25 Jul 2004 13:59:51

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

Yes, I screwed up.

SUMPRODUCT probably is best for this.
 
 
 

Number of n's in a range

Post by Anderso » Mon, 26 Jul 2004 05:21:20

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

Post by Anderso » Mon, 26 Jul 2004 05:28:06

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

Number of n's in a range

Post by Harlan Gro » Mon, 26 Jul 2004 06:37:18

"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

Post by Anderso » Mon, 26 Jul 2004 06:51:18

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.