In cell C2 I have the following formula that works fine for what I need it
for.

=SUMPRODUCT(($L1:$L23<$B1)*($M1:$M23<=$A$1)*($L13: $L23=$K1))

In the cells in column J i have a list of numbers that are the same in the
following different ranges as follows
J2:J23 = 123456
J24:J30 = 123457
J31 = 123458
J32:J33 = 123459
etc.

I copy the formula in cell C2 down the column and then I have to manually
change the range in the formula to match the ranges of other columns. So the
following cells will have the following formulas in. The rest of the cells in
the range are deleted.

C2 =SUMPRODUCT(($L2:$L23<$B1)*($M2:$M23<=$A$1)*($L2:$ L23=$K1))
C24 =SUMPRODUCT(($L24:$L30<$B1)*($M24:$M30<=$A$1)*($L2 4:$L30=$K1))
C31 =SUMPRODUCT(($L31:$L31<$B1)*($M31:$M31<=$A$1)*($L3 1:$L31=$K1))
C32 =SUMPRODUCT(($L32:$L33<$B1)*($M32:$M33<=$A$1)*($L3 2:$L33=$K1))

What I need is to copy the formula in cell C2 down the page so it displays
the answer to the formula in the first cell where the entries in the ranges
in column J are the same but blanks are in the rest of the cells in the range.

e.g.
Cell C2 =SUMPRODUCT(($L2:$L23<$B1)*($M2:$M23<=$A$1)*($L2:$ L23=$K1))
Cell C3 BLANK
etc.,
Cell C23 BLANK

Cell C24 =SUMPRODUCT(($L24:$L30<$B1)*($M24:$M30<=$A$1)*($L2 4:$L30=$K1))
Cell C25 BLANK
etc.,
Cell C30 BLANK

Cell C31 =SUMPRODUCT(($L31:$L31<$B1)*($M31:$M31<=$A$1)*($L3 1:$L31=$K1))

Cell C32 =SUMPRODUCT(($L32:$L33<$B1)*($M32:$M33<=$A$1)*($L3 2:$L33=$K1))
Cell C33 BLANK

I hope this makes sense.

Thank you in advance for any help.

Just copy them down across J2:J32, then delete the ones you don't want, it
will take 10 seconds.

--

HTH

Bob

"Mally" wrote in message
...
In cell C2 I have the following formula that works fine for what I need it
for.

=SUMPRODUCT(($L1:$L23<$B1)*($M1:$M23<=$A$1)*($L13: $L23=$K1))

In the cells in column J i have a list of numbers that are the same in the
following different ranges as follows
J2:J23 = 123456
J24:J30 = 123457
J31 = 123458
J32:J33 = 123459
etc.

I copy the formula in cell C2 down the column and then I have to manually
change the range in the formula to match the ranges of other columns. So
the
following cells will have the following formulas in. The rest of the cells
in
the range are deleted.

C2 =SUMPRODUCT(($L2:$L23<$B1)*($M2:$M23<=$A$1)*($L2:$ L23=$K1))
C24 =SUMPRODUCT(($L24:$L30<$B1)*($M24:$M30<=$A$1)*($L2 4:$L30=$K1))
C31 =SUMPRODUCT(($L31:$L31<$B1)*($M31:$M31<=$A$1)*($L3 1:$L31=$K1))
C32 =SUMPRODUCT(($L32:$L33<$B1)*($M32:$M33<=$A$1)*($L3 2:$L33=$K1))

What I need is to copy the formula in cell C2 down the page so it displays
the answer to the formula in the first cell where the entries in the
ranges
in column J are the same but blanks are in the rest of the cells in the
range.

e.g.
Cell C2 =SUMPRODUCT(($L2:$L23<$B1)*($M2:$M23<=$A$1)*($L2:$ L23=$K1))
Cell C3 BLANK
etc.,
Cell C23 BLANK

Cell C24 =SUMPRODUCT(($L24:$L30<$B1)*($M24:$M30<=$A$1)*($L2 4:$L30=$K1))
Cell C25 BLANK
etc.,
Cell C30 BLANK

Cell C31 =SUMPRODUCT(($L31:$L31<$B1)*($M31:$M31<=$A$1)*($L3 1:$L31=$K1))

Cell C32 =SUMPRODUCT(($L32:$L33<$B1)*($M32:$M33<=$A$1)*($L3 2:$L33=$K1))
Cell C33 BLANK

I hope this makes sense.

Thank you in advance for any help.