I have two sheets, one called All Project Net Profit with row data spilling across the rows with month/year dates and the Net Profit total in row 13. The following is a sample of the data:

The other sheet called Yearly Net Profit calculates the yearly Net Profit using the formula =SUM((--TEXT('All Projects Net Profit'!$I$13#,"£0,00")*(1*A2=--'All Projects Net Profit'!$I$1#))) pasted in to B2.

For now I have manually copied the formula down the columns but is there a way to make it spill for the amount of entries in Column A?

Sample data for the Yearly Net Profit sheet:

CodePudding user response：

Using BYROW:

=BYROW(  A2:A6,
 LAMBDA( x,
SUM((--TEXT('All Projects Net Profit'!$I$13#,"£0,00")*(1*x=--'All Projects Net Profit'!$I$1#)))))

You first declare the range you want to use your formula: A2:A6;

Then you use LAMBDA to name that byrow-range: x;

Then use your "regular" formula and replace the A2 from your original formula, that you want to do byrow with x.

Done.