CountIf Conditions / Occurrences
 

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

CountIf Conditions / Occurrences
 

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

CountIf Conditions / Occurrences
 

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

CountIf Conditions / Occurrences
 

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

CountIf Conditions / Occurrences
 

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

CountIf Conditions / Occurrences
 

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

CountIf Conditions / Occurrences
 

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