I am trying to fill in a table that needs to look at two different columns to
see whether to to count the data. For example, here is some data:
A B
1 10 3
2 40 2.5
3 71 4
4 124 3.5

Now to create the table, I want to know how many times something occurs
between values looking at both conditions with column B data across the top
and column A data along the veritcal, as below:

0-1 1.1-1.5 1.6-2 2.1-2.5 2.6-3 3.1-3.5 4 or more
0-40
41-80
81-120
121-160

Any ideas? I've tried erally long arrays but it's getting really comlicated
as I add more data.

Here is the data and formulas I used. I put the results in a table located
at A10:A14. I also changed the Left column and top row of the table to
include only the lower n umber of the range.

the formula works by looking at the left column and comparing the number in
the cell with the number at the left column and the number in the left column
in the next row. Again in the top row it compares the cell with the number
in the top row and the number in the top row in the next column.

the formulas can be copied except in the last Row and the Last column which
are missing terms.

Table in A10:H14

0 1 1.5 2 2.5 3 4
0 0 0 0 0 1 1 0
41 0 0 0 0 0 0 1
81 0 0 0 0 0 0 0
121 0 0 0 0 0 1 0

B11:B14
=SUMPRODUCT(--($A$1:$A$4=$A11),--($A$1:$A$4<$A12),--($B$1:$B$4=B$10),--($B$1:$B$4<C$10))
=SUMPRODUCT(--($A$1:$A$4=$A12),--($A$1:$A$4<$A13),--($B$1:$B$4=B$10),--($B$1:$B$4<C$10))
=SUMPRODUCT(--($A$1:$A$4=$A13),--($A$1:$A$4<$A14),--($B$1:$B$4=B$10),--($B$1:$B$4<C$10))
=SUMPRODUCT(--($A$1:$A$4=$A14),--($B$1:$B$4=B$10),--($B$1:$B$4<C$10))

C11:c14
=SUMPRODUCT(--($A$1:$A$4=$A11),--($A$1:$A$4<$A12),--($B$1:$B$4=C$10),--($B$1:$B$4<D$10))
=SUMPRODUCT(--($A$1:$A$4=$A12),--($A$1:$A$4<$A13),--($B$1:$B$4=C$10),--($B$1:$B$4<D$10))
=SUMPRODUCT(--($A$1:$A$4=$A13),--($A$1:$A$4<$A14),--($B$1:$B$4=C$10),--($B$1:$B$4<D$10))
=SUMPRODUCT(--($A$1:$A$4=$A14),--($B$1:$B$4=C$10),--($B$1:$B$4<D$10))

D11:D14
=SUMPRODUCT(--($A$1:$A$4=$A11),--($A$1:$A$4<$A12),--($B$1:$B$4=D$10),--($B$1:$B$4<E$10))
=SUMPRODUCT(--($A$1:$A$4=$A12),--($A$1:$A$4<$A13),--($B$1:$B$4=D$10),--($B$1:$B$4<E$10))
=SUMPRODUCT(--($A$1:$A$4=$A13),--($A$1:$A$4<$A14),--($B$1:$B$4=D$10),--($B$1:$B$4<E$10))
=SUMPRODUCT(--($A$1:$A$4=$A14),--($B$1:$B$4=D$10),--($B$1:$B$4<E$10))

E11:E14
=SUMPRODUCT(--($A$1:$A$4=$A11),--($A$1:$A$4<$A12),--($B$1:$B$4=E$10),--($B$1:$B$4<F$10))
=SUMPRODUCT(--($A$1:$A$4=$A12),--($A$1:$A$4<$A13),--($B$1:$B$4=E$10),--($B$1:$B$4<F$10))
=SUMPRODUCT(--($A$1:$A$4=$A13),--($A$1:$A$4<$A14),--($B$1:$B$4=E$10),--($B$1:$B$4<F$10))
=SUMPRODUCT(--($A$1:$A$4=$A14),--($B$1:$B$4=E$10),--($B$1:$B$4<F$10))

F11:f14
=SUMPRODUCT(--($A$1:$A$4=$A11),--($A$1:$A$4<$A12),--($B$1:$B$4=F$10),--($B$1:$B$4<G$10))
=SUMPRODUCT(--($A$1:$A$4=$A12),--($A$1:$A$4<$A13),--($B$1:$B$4=F$10),--($B$1:$B$4<G$10))
=SUMPRODUCT(--($A$1:$A$4=$A13),--($A$1:$A$4<$A14),--($B$1:$B$4=F$10),--($B$1:$B$4<G$10))
=SUMPRODUCT(--($A$1:$A$4=$A14),--($B$1:$B$4=F$10),--($B$1:$B$4<G$10))

G11:G14
=SUMPRODUCT(--($A$1:$A$4=$A11),--($A$1:$A$4<$A12),--($B$1:$B$4=G$10),--($B$1:$B$4<H$10))
=SUMPRODUCT(--($A$1:$A$4=$A12),--($A$1:$A$4<$A13),--($B$1:$B$4=G$10),--($B$1:$B$4<H$10))
=SUMPRODUCT(--($A$1:$A$4=$A13),--($A$1:$A$4<$A14),--($B$1:$B$4=G$10),--($B$1:$B$4<H$10))
=SUMPRODUCT(--($A$1:$A$4=$A14),--($B$1:$B$4=G$10),--($B$1:$B$4<H$10))

H11:H14
=SUMPRODUCT(--($A$1:$A$4=$A11),--($A$1:$A$4<$A12),--($B$1:$B$4=H$10))
=SUMPRODUCT(--($A$1:$A$4=$A12),--($A$1:$A$4<$A13),--($B$1:$B$4=H$10))
=SUMPRODUCT(--($A$1:$A$4=$A13),--($A$1:$A$4<$A14),--($B$1:$B$4=H$10))
=SUMPRODUCT(--($A$1:$A$4=$A14),--($B$1:$B$4=H$10))



"bunky2000" wrote:

I am trying to fill in a table that needs to look at two different columns to
see whether to to count the data. For example, here is some data:
A B
1 10 3
2 40 2.5
3 71 4
4 124 3.5

Now to create the table, I want to know how many times something occurs
between values looking at both conditions with column B data across the top
and column A data along the veritcal, as below:

0-1 1.1-1.5 1.6-2 2.1-2.5 2.6-3 3.1-3.5 4 or more
0-40
41-80
81-120
121-160

Any ideas? I've tried erally long arrays but it's getting really comlicated
as I add more data.