I have data arranged as follows;


A B C D
1 blank
2 blank
3 7 0 3 4
4 0 3 3 1
5 blank
6 blank
7 3 4 3 4
8 0 0 3 1
9 blank
10 blank

The data range is large with the above layout. What I am trying to develop
is a function / routine that will give me the number of occurrences in range
A1:D10 of (0 - 3 or 3 - 0) which would = 2.

This is just for fun... don't waste time on it, from what I have found it
looks very difficult. But maybe it's simple.

Thanks,
Ronbo

Don't know what you mean.0-3 or 3-0? Do you mean all in one cell or 0 in
one cell and 3 in adjacent cell in the same row or same column. I don't
see how you get 2 from your example


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=508591

Hi!

Why is the expected result 2 and not 3?

Biff

"Ronbo" wrote in message
...
I have data arranged as follows;


A B C D
1 blank
2 blank
3 7 0 3 4
4 0 3 3 1
5 blank
6 blank
7 3 4 3 4
8 0 0 3 1
9 blank
10 blank

The data range is large with the above layout. What I am trying to
develop
is a function / routine that will give me the number of occurrences in
range
A1:D10 of (0 - 3 or 3 - 0) which would = 2.

This is just for fun... don't waste time on it, from what I have found it
looks very difficult. But maybe it's simple.

Thanks,
Ronbo

Hmmm.....

I think I figured it out. I don't know about doing this with just a single
formula, though.

Maybe in cell F4 enter this formula as an array using the key combo of
CTRL,SHIFT,ENTER:

=SUMPRODUCT(--(ISNUMBER(FIND({"03","30"},TRANSPOSE(A3:D3&A4:D4)) )))

Copy into cell F8

Then, just do a Sum on column F:

=SUM(F:F)

Biff

"Biff" wrote in message
...
Hi!

Why is the expected result 2 and not 3?

Biff

"Ronbo" wrote in message
...
I have data arranged as follows;


A B C D
1 blank
2 blank
3 7 0 3 4
4 0 3 3 1
5 blank
6 blank
7 3 4 3 4
8 0 0 3 1
9 blank
10 blank

The data range is large with the above layout. What I am trying to
develop
is a function / routine that will give me the number of occurrences in
range
A1:D10 of (0 - 3 or 3 - 0) which would = 2.

This is just for fun... don't waste time on it, from what I have found it
looks very difficult. But maybe it's simple.

Thanks,
Ronbo

They are in pairs. In other words 7-0 is a pair, 0-3 is a pair, 3-3 is a
pair and 4-1 is a pair. Thus in the first set of data 0-3 occures once. In
the second set of data 3-0 occures once, so I would have two occurrences ( it
is either way 3-0 or 0-3)

"Biff" wrote:

Hi!

Why is the expected result 2 and not 3?

Biff

"Ronbo" wrote in message
...
I have data arranged as follows;


A B C D
1 blank
2 blank
3 7 0 3 4
4 0 3 3 1
5 blank
6 blank
7 3 4 3 4
8 0 0 3 1
9 blank
10 blank

The data range is large with the above layout. What I am trying to
develop
is a function / routine that will give me the number of occurrences in
range
A1:D10 of (0 - 3 or 3 - 0) which would = 2.

This is just for fun... don't waste time on it, from what I have found it
looks very difficult. But maybe it's simple.

Thanks,
Ronbo

OK, that's what the formula in my other reply does.

Biff

"Ronbo" wrote in message
...
They are in pairs. In other words 7-0 is a pair, 0-3 is a pair, 3-3 is a
pair and 4-1 is a pair. Thus in the first set of data 0-3 occures once.
In
the second set of data 3-0 occures once, so I would have two occurrences
( it
is either way 3-0 or 0-3)

"Biff" wrote:

Hi!

Why is the expected result 2 and not 3?

Biff

"Ronbo" wrote in message
...
I have data arranged as follows;


A B C D
1 blank
2 blank
3 7 0 3 4
4 0 3 3 1
5 blank
6 blank
7 3 4 3 4
8 0 0 3 1
9 blank
10 blank

The data range is large with the above layout. What I am trying to
develop
is a function / routine that will give me the number of occurrences in
range
A1:D10 of (0 - 3 or 3 - 0) which would = 2.

This is just for fun... don't waste time on it, from what I have found
it
looks very difficult. But maybe it's simple.

Thanks,
Ronbo

This is even better and is not an array. Normally entered:

=SUMPRODUCT(--(ISNUMBER(MATCH(A3:D3&A4:D4,{"03","30"},0))))

Biff

"Biff" wrote in message
...
Hmmm.....

I think I figured it out. I don't know about doing this with just a single
formula, though.

Maybe in cell F4 enter this formula as an array using the key combo of
CTRL,SHIFT,ENTER:

=SUMPRODUCT(--(ISNUMBER(FIND({"03","30"},TRANSPOSE(A3:D3&A4:D4)) )))

Copy into cell F8

Then, just do a Sum on column F:

=SUM(F:F)

Biff

"Biff" wrote in message
...
Hi!

Why is the expected result 2 and not 3?

Biff

"Ronbo" wrote in message
...
I have data arranged as follows;


A B C D
1 blank
2 blank
3 7 0 3 4
4 0 3 3 1
5 blank
6 blank
7 3 4 3 4
8 0 0 3 1
9 blank
10 blank

The data range is large with the above layout. What I am trying to
develop
is a function / routine that will give me the number of occurrences in
range
A1:D10 of (0 - 3 or 3 - 0) which would = 2.

This is just for fun... don't waste time on it, from what I have found
it
looks very difficult. But maybe it's simple.

Thanks,
Ronbo