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

In cell E1 put this formula and copy down the column

=COUNTIF(A1:D1,1)

"Jack" wrote:

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

Assuming your numbers data in A1:D1 down
In E1: =SUMPRODUCT(--ISNUMBER(MATCH({1,2},A1:D1,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
---
"Jack" wrote:
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???

In my real dataset, I have 1090 rows.

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

=SUMPRODUCT(--(MMULT(--(A1:D1090=1),{1;1;1;1})0),--(MMULT(--(A1:D1090=2),{1;1;1;1})0))

--
Biff
Microsoft Excel MVP


"Jack" wrote in message
...
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???