'smart' dragging
 

how can i enter this formula in the spreadsheet so I can
drag it where the A2 only changes relative to the cell ie

=SUMPRODUCT((Invoices!A5:A1000=A2)*(Invoices!G5:G1 000))
drag this one to get....
=SUMPRODUCT((Invoices!A5:A1000=A3)*(Invoices!G5:G1 000))
=SUMPRODUCT((Invoices!A5:A1000=A4)*(Invoices!G5:G1 000))
etc when I drag all the figures change to this
=SUMPRODUCT((Invoices!A6:A1001=A23)*(Invoices!G6:G 1001)) etc

:(

thanks in advance

'smart' dragging
 

Use absolute references

=SUMPRODUCT((Invoices!$A$5:$A$1000=A2)*(Invoices!$ G$5:$G$1000))

Mike

"Spheon" wrote:

how can i enter this formula in the spreadsheet so I can
drag it where the A2 only changes relative to the cell ie

=SUMPRODUCT((Invoices!A5:A1000=A2)*(Invoices!G5:G1 000))
drag this one to get....
=SUMPRODUCT((Invoices!A5:A1000=A3)*(Invoices!G5:G1 000))
=SUMPRODUCT((Invoices!A5:A1000=A4)*(Invoices!G5:G1 000))
etc when I drag all the figures change to this
=SUMPRODUCT((Invoices!A6:A1001=A23)*(Invoices!G6:G 1001)) etc

:(

thanks in advance

'smart' dragging
 

thanks....all this time and now I know the difference :)

"Mike H" wrote:

Use absolute references

=SUMPRODUCT((Invoices!$A$5:$A$1000=A2)*(Invoices!$ G$5:$G$1000))

Mike

"Spheon" wrote:

how can i enter this formula in the spreadsheet so I can
drag it where the A2 only changes relative to the cell ie

=SUMPRODUCT((Invoices!A5:A1000=A2)*(Invoices!G5:G1 000))
drag this one to get....
=SUMPRODUCT((Invoices!A5:A1000=A3)*(Invoices!G5:G1 000))
=SUMPRODUCT((Invoices!A5:A1000=A4)*(Invoices!G5:G1 000))
etc when I drag all the figures change to this
=SUMPRODUCT((Invoices!A6:A1001=A23)*(Invoices!G6:G 1001)) etc

:(

thanks in advance