SUMPRODUCT over ranges

  • Thread starter Thread starter Gerrym
  • Start date Start date
G

Gerrym

I have 400 rows in my sheet and I am using Sumproduct on
the amount column. It all works fine except I have to
manually edit the range of cells in the formula as I add
records. If if extent the range to more rows than there
is informatio i.e. blank rows I get an #N/A Error. Can
this be fixed.
 
Hi

My formula is =SUMPRODUCT((Invoices!$M$2:$M$280='P&L
Totals'!$A14)*(TEXT(Invoices!$G$2:$G$280,"mmmm")='P&L
Totals'!H$2)*(Invoices!$H$2:$H$280)/1000)

It is when I increase the rows past 280 if there is nothing
in them that I get #N/A. Basically I would like to be
able have the formula read the full range as I add to it
in the Invoices sheet
 
Range Invoices!M2:M280 has a P&L Code (No Blanks) P&L!A14
P&L CODE (No Blanks)Invoices!G2:G280 Dates(dd/mm/yyyy),
P&L Totals!H2 Month( July etc)and Invoices!H2:H280
amounts. There are no blank rows so no #N/A. If I add
rows to Invoices which I do regularly as this is a list of
incoming invoices I must manually amend the formula on P&L
to extent from 280 to the extra rows. I would like to
have the formula read rows to 750 even when they are blank
as this would cover all invoices for 1 year.
 
Hi
post the formula which results in this error. Just make sure that you
extend ALL ranges accordingly
 
Back
Top