Forum Discussion
Summing positive/negative amounts across sheets
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.
I entered the following into my 2nd sheet (Stats).
=SUMPRODUCT('Funded Bootcamp.xlsm'!Profit.S:Profit.E * (+('Funded Bootcamp.xlsm'!Profit.S:Profit.E)>0)) =SUMPRODUCT('Funded Bootcamp.xlsm'!Profit.S:Profit.E * (+('Funded Bootcamp.xlsm'!Profit.S:Profit.E) < 0)) =SUM(INDEX('Funded Bootcamp.xlsm'!Profit.S:Profit.E,AGGREGATE(14, 6, 1/('Funded Bootcamp.xlsm'!Profit.S:Profit.E>0)*ROW('Funded Bootcamp.xlsm'!Profit.S:Profit.E), {1,2,3}))) =SUM(INDEX('Funded Bootcamp.xlsm'!Profit.S:Profit.E,AGGREGATE(14, 6, 1/('Funded Bootcamp.xlsm'!Profit.S:Profit.E < 0) * ROW('Funded Bootcamp.xlsm'!Profit.S:Profit.E), {1,2,3})))
Am getting result = FALSE. I do not see a typing error, but maybe you will.
My Spread Sheet name is "Funded Bootcamp.xlsm" and my two sheets are "Orders" and "STATS_1". The Profit column is Column "W" and I have Defined Names in cell "W2" and at this point in cell "W10856", quite a bit of data here.
Hope this additional info plus corrections to original posting clarifies my needs somewhat.
Sorry, I didn't catch. If Profit is the name of the column, e.g. =Orders!$W:$W, what is Profit.S, etc. ?
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.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.