Jul 10 2022 06:03 AM - edited Jul 10 2022 11:14 AM
Message Edited. Sorry for my mistakes in the original.
----------------------------------------------------------------
Sum Profit and/or Loss from a column from 1 sheet (Raw) in 2nd sheet (Stats), for a set number of rows.
Data sheet 1 (Raw):
100 rows of pos./neg. amounts in a column in sheet 1(Raw). Want formula to find total pos./neg. amts. in the last 10 rows (This number is variable by the user, could be any pos. number from 1 to infinity). Ex: last 10 rows contain 7 rows with pos. amts. and 3 rows containing neg. amts. Two formulas, Case 1:one summing pos. amts. and Case 2:one summing neg. amts.
Case 1:
1 $1.00
2 $0.23
3 -$3.32
4 $22.65
5 -$2.10
6 $78.44
7 $19.46
8 $19.72
9 $1974
10 -$16.45
11 $22.20
12 -$100.75
Results on the Stats Sheet should be Positive Amount = $161.24 and Negative Amount = -$21.87.
Case 2: On second sheet, find Pos. and Neg. amounts for the last 3 Positive Amounts and for the last 3 Negative Amounts.
Results: Last(Most recent- Rows 1, 2, 3) 3 Positive Amounts = $23.88. Last(Most recent 2, 5, 10) 3 Negative Amounts = -$21.87.
I have struggled with these two calculations for quite so time and would really be so GRATEFUL for some solution.
Thanks
Jul 10 2022 07:03 AM
It depends on which Excel version / platform you are. As variant
=SUMPRODUCT( data * ( SIGN(data) > 0) )
=SUMPRODUCT( data * ( SIGN(data) < 0) )
=SUM( INDEX( data, AGGREGATE(14, 6, 1/(data>0)*ROW(data), {1,2,3} ) ) )
=SUM( INDEX( data, AGGREGATE(14, 6, 1/(data<0)*ROW(data), {1,2,3} ) ) )
assuming data is from first row, otherwise to adjust a bit.
Jul 10 2022 11:30 AM
Jul 11 2022 05:53 AM
Sorry, I didn't catch. If Profit is the name of the column, e.g. =Orders!$W:$W, what is Profit.S, etc. ?
Jul 11 2022 06:12 AM
Good Morning Sergei.
Profit.S is Defined Name for the start of Profit column. i.e. Column W, row 2, or $W$2 in my sheet "Orders". Profit.E is highest row in same column. It increases as I insert new rows into the "Orders" sheet using "Insert copied cells", currently, in my up to date spreadsheet it is W10855. Row 1 is most recent data and row 10855 is oldest data. Just an FYI, this spreadsheet is a record of my FOREX Trading going back to October 2021. Hope that I am making sense to you, please tell me if I need to explain more or better.
Jul 11 2022 12:46 PM
If in first file we have
when in another file
formula
=SUMPRODUCT(
'Funded Bootcamp.xlsm'!Profit.S:'Funded Bootcamp.xlsm'!Profit.E *
('Funded Bootcamp.xlsm'!Profit.S:'Funded Bootcamp.xlsm'!Profit.E>0))
works. Perhaps the same for other formulae.