# Summing positive/negative amounts across sheets

Copper Contributor

# Summing positive/negative amounts across sheets

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.32

4  \$22.65

-\$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

5 Replies

# Re: 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.

# Re: Summing positive/negative amounts across sheets

Thank you very much Sergei.
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.

# Re: Summing positive/negative amounts across sheets

Sorry, I didn't catch. If Profit is the name of the column, e.g. =Orders!\$W:\$W, what is Profit.S, etc. ?

# Re: Summing positive/negative amounts across sheets

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.

# Re: Summing positive/negative amounts across sheets

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.