SUM Product Help

[sgibbs183]

Hi Have the below sum product formula which works fine, but I want to extend the bold rows to say 1000 rows in case the next download I do of the data is more rows, so I won't have to keep changing the formula.

=SUMPRODUCT(('https://poluk.sharepoint.com/sites/FP&A team/Shared Documents/2022-23 Change/Change - Business Case/New Model/[Anaplan Exports Master.xlsx]Spend Forecast'!$A$2:$A$692=$A$2)*('https://poluk.sharepoint.com/sites/FP&A team/Shared Documents/2022-23 Change/Change - Business Case/New Model/[Anaplan Exports Master.xlsx]Spend Forecast'!$C$2:$C$692=$A4)*('https://poluk.sharepoint.com/sites/FP&A team/Shared Documents/2022-23 Change/Change - Business Case/New Model/[Anaplan Exports Master.xlsx]Spend Forecast'!$E$1:$DR$1=B$2)*'https://poluk.sharepoint.com/sites/FP&A team/Shared Documents/2022-23 Change/Change - Business Case/New Model/[Anaplan Exports Master.xlsx]Spend Forecast'!$E$2:$DR$692)

When I change this to the below I get #REF! error. Why is it doing this?

SUMPRODUCT(('https://poluk.sharepoint.com/sites/FP&A team/Shared Documents/2022-23 Change/Change - Business Case/New Model/[Anaplan Exports Master.xlsx]Spend Forecast'!$A$2:$A$1000=$A$2)*('https://poluk.sharepoint.com/sites/FP&A team/Shared Documents/2022-23 Change/Change - Business Case/New Model/[Anaplan Exports Master.xlsx]Spend Forecast'!$C$2:$C$1000=$A4)*('https://poluk.sharepoint.com/sites/FP&A team/Shared Documents/2022-23 Change/Change - Business Case/New Model/[Anaplan Exports Master.xlsx]Spend Forecast'!$E$1:$DR$1=B$2)*'https://poluk.sharepoint.com/sites/FP&A team/Shared Documents/2022-23 Change/Change - Business Case/New Model/[Anaplan Exports Master.xlsx]Spend Forecast'!$E$2:$DR$1000)

Help will be much appreciated

Thanks!

 

[awoohaw]

What kind of data is in column A, column C, the cell B2, the header row E:DR, and the grid E2:DR1000?
Is A:A, C:C numeric? B2 a date, E1:DR1 a series of dates? What data is E2:DR692?

You can help the people help you by using the xl2bb addin and posting some sample data and your cell formulas.

Two things I noticed, and I'm not sure if it is a problem,
you're starting the comparison for the first array with the fixed first value of column A, (A:A=$A$2)
the cell in the second array is being compared to a value in the column of the first array (C:C=$A4). but that seems to be in the good and bad formula .