Automatically change ranges when copying formula

[Mally]

I have the following formulas in my worksheet

Row 2 =SUMPRODUCT(($N2:$N9<$H2)*($O2:$O9<$G$1)*($N2:$N9>=$L2))
Row 10 =SUMPRODUCT(($N10:$N12<$H10)*($O10:$O12<$G$1)*($N10:$N12>=$L10))
Row 13 =SUMPRODUCT(($N13:$N20<$H13)*($O13:$O20<$G$1)*($N13:$N20>=$L13))

As you can see there are different ranges in the formulas. What I am doing
is copying the formulas into the relevant cells and manually changing the
ranges. Having over 3000 to do will take a lot of time!

Is there and easy way of copying the formula down the sheet to pick up the
ranges.

Each new range row is highlighted in TAN so I don’t know if this could be
used to find the new range.

Any help would be appreciated.

 

[Francis]

perhap, try using Name range, such as
naming col N's data to its header and use the name in the formula.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Sheeloo]

Do NOT use $ in front of the Col letter and/or Row number if you want it to
CHANGE while copying. $ indicates absolute references which do not change...
like in $G$1 below

Try in row 2
=SUMPRODUCT((N2:N9<H2)*(O2:O9<$G$1)*(N2:N9>=L2))

This will change to
=SUMPRODUCT((N10:N17<H10)*(O10:O17<$G$1)*(N10:N17>=L10))
when copied to row 10

and to
=SUMPRODUCT((N13:N20<H13)*(O13:O20<$G$1)*(N13:N20>=L13))
in row 13

 

[Mally]

Hi Francis

Thanks for your reply. I'm having a look at the name range to see if it
will work.

 

[Mally]

Hi Sheeloo

Thank you for your reply.

The ranges specified in the formulas are the ranges that i need so by taking
out the $ and copying the formulas will pick up the incorrect ranages.

Thank you again.

 

[Sheeloo]

Mally,
Sorry. I did not notice that the second formula was different...
Where are you copying these to? I mean which rows will have the formula in
row 2, which rows will have the formula in row 10, row13?
If the formulas follow the same pattern then enter thee formulas below in
the row indicated, select row 1-13 and drag down....

row 2 =SUMPRODUCT((N2:N9<H2)*(O2:O9<$G$1)*(N2:N9>=L2))
row 10 =SUMPRODUCT((N10:N12<H10)*(O10:O12<$G$1)*(N10:N17>=L10))
row 13 =SUMPRODUCT((N13:N20<H13)*(O13:O20<$G$1)*(N13:N20>=L13))

 

[Mally]

Hi again Sheeloo

Thank you for your reply.

My problem is that there is no pattern to my formulas. The ranges are
different in every formula i.e. each individual row formula looks at a
different number of cells in a range. So thats why i can't copy it down a
column.

 

[Sheeloo]

If there is no pattern then you will have to do it manually...