• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum of negative subtotal

P

Pierre

Member
Hello all,

This is a bit tricky topic on which I have been pulling my hair (or what's left of it) for the last 2 days. I have a big data base of stock movement with many information, the most important being the family, movement code and type.

For each movement, I created a summary table with all the families in one row (I only put 2 in the data example, but I have many more in reality), and the 3 movement code I'm interested in (they are fixed, not interested in other movement code) in different column. I make a sum of every movement for every family, then make the sum. I only keep the negative sum to calculate my total.

The final step is to have a summary table with the total of each type in order to calculate the "grand negative total"

The thing is that I have many different types, and it is cumbersome to create lots of intermediary table, and I'm only interested in the "grand negative total".

Is there a way to achieve with a formula?

I enclosed a simplified extract of my data, the different table, and the grand total I'm trying to calculate directly

Thank you in advance for any advice on how I could do!
 

Attachments

  • sum_negative_subtotal.xlsx
    11.7 KB · Views: 11
Hello Pierre.
Please try this

For Type 10, in cell H12:

Code: 
=SUMIFS($H$3:$J$8,$G$3:$G$8,H$11,$I$3:$I$8,"<>")+SUMIFS($H$14:$J$19,$G$14:$G$19,H$11,$I$14:$I$19,"<>")

For Type 20, in cell H13:

Code: 
=SUMIFS($H$3:$J$8,$G$3:$G$8,H$12,$I$3:$I$8,"<>")+SUMIFS($H$14:$J$19,$G$14:$G$19,H$12,$I$14:$I$19,"<>")

For the Grand Negative Total, in cell H14:

Code: 
=SUM(H12:H13)


This formula sums the negative values for each type and then calculates the Grand Negative Total by summing the results for all types. Adjust the cell references based on the actual location of your data in your worksheet.
 
In J25:
Code: 
=SUM(BYROW(SUMIFS($F$3:$F$13,$D$3:$D$13,J$5:L$5,$C$3:$C$13,$I25,$G$3:$G$13,$J$23,$B$3:$B$13,$I$6:$I$7),LAMBDA(a,IF(SUM(a)<0,SUM(a),""))))
copy down.
 
p45cal said:
In J25:
Code: 
=SUM(BYROW(SUMIFS($F$3:$F$13,$D$3:$D$13,J$5:L$5,$C$3:$C$13,$I25,$G$3:$G$13,$J$23,$B$3:$B$13,$I$6:$I$7),LAMBDA(a,IF(SUM(a)<0,SUM(a),""))))
copy down.
Click to expand...
Hi @p45cal,

Works very well, thank you!

I'm trying to understand exactly how it works, I'm struggling with the LAMBDA function. I've never seen it in a formula, could you explain what's its role?

Thanks!
 
Hi @Monty,

Thank you for your answer and I appreciate the effort, however the ranges don't match with the ranges in the original file. For example in column H there is not data in my file

Maybe you moved columns on your side and it doesn't reflect the position on the original file.
 
pecoflyer said:
The OP being so prolific on this forum in every aspect of XL, I suspect there is some AI at work...
Click to expand...
I'm 99% sure that is true, which would be fine if any of the replies were even lightly tested first. Both of those SUMIFS formulas would simply return a #VALUE error since the ranges are not the same size... I just don't see the point.
 

Monty​

About Your
#1 Please do let me know if you are facing any issue
... Please, try to test 'Your' solutions too
#2 Adjust the cell references based on the actual location of your data in your worksheet.
... Above sentence could read that You've 'gotten' Your solution somewhere ... reread #1
... or try to explain eg that H-column case.
 
Pierre said:
I'm struggling with the LAMBDA function. I've never seen it in a formula, could you explain what's its role?
Click to expand...
It would take me a long time to give you a tutorial on LAMBDA and its stablemate BYROW; you should be able to find plenty of that on the interweb.

However, have an explore using your spreadsheet:
In, say, cell P6:
Code: 
=BYROW(J6:L7,LAMBDA(a,SUM(a)))

and, as a next step, a similar one in cell R6:
Code: 
=BYROW(J6:L7,LAMBDA(a,IF(SUM(a)<0,SUM(a),"")))
 
Will do!
Thanks for your help, it has been very useful and helped me a great deal!
I will explore about this BYROW and LAMBDA :)
 
You must log in or register to reply here.
Back
Top