CountIf Conditions / Occurrences

R

Ronbo

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
 
D

daddylonglegs

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
 
B

Biff

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
 
R

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)
 
B

Biff

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

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

Biff
 
Top