Home > Blockchain >  Spill Sum formula in column excel
Spill Sum formula in column excel

Time:01-17

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:

2017 2017 2017 2017 2017 2018 2018 2018 2018 2018 2018 2018 2018
Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18
-22596 77104 81604 81604 81604 81604 81604 81604 81604 81604 81604 81604 81604

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:

Year Net Profit
2017 299320
2018 979248
2019 943813
2020 -206450
2021 -763368

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.

  • Related