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!
=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!