Counting numbers in a series

[Jack]

Hi Everyone

I have a problem that's driving me crazy.

Say this is my data set

Row 1: 1 2 3 4
Row 2: 2 5 6 1
Row 3: 2 4 1 8
Row 4: 2 3 6 7

I want to find how many times the numbers 1 and 2 appear in each row. In
the dataset above, the answer would be 3 (i.e. rows 1, 2 and 3). In my real
dataset, I have 1090 rows. Anyone got any ideas???

 

[Joel]

In cell E1 put this formula and copy down the column

=COUNTIF(A11,1)

 

[Max]

Assuming your numbers data in A11 down
In E1: =SUMPRODUCT(--ISNUMBER(MATCH({1,2},A11,0)))
Copy E1 down as far as required. Returns 2 where the condition is satisfied.
Then in F1: =COUNTIF(E:E,2) will give the result that you seek
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik

 

[T. Valko]

In my real dataset, I have 1090 rows.

This formula is limited to 5461 rows so you're well within that limit.

=SUMPRODUCT(--(MMULT(--(A11090=1),{1;1;1;1})>0),--(MMULT(--(A11090=2),{1;1;1;1})>0))