Formula to subtotal values based on multiple criteria

This is my first post in the community. I hope I have attached the sample data properly!
I think I am overcomplicating this problem and could really use some help! I've created some sample data, but the spreadsheet I will be working with is much larger and will be updated weekly with new data. I'm using Excel 365.

I need to sum GM values for invoices that have trade status of "Not Loaded". I've highlighted the GM values that I need the formula to sum. I would like to subtotal values so when rows are filtered the total adjusts. Any thoughts or advice will be greatly appreciated!!


Example Data.xlsx

hi everyone, I allow me to propose this possible solution:

Please Login or Register  to view this content.

I hope it result useful.

Example Data (Solution).xlsx

Cell I42 formula

Formula:

Please Login or Register  to view this content.

Thank you DJunqueira for pointing out cell B10 that I missed when calculating a total. I tried to use your formula, but it returned "0". Do you know what I could have done wrong? I thought this would work. I copied the filter portion of the formula into another cell and I see that it returns the correct invoice numbers, but I don't understand why it will not sum. I evaluated the formula and I don't understand why this is happening: SUM({0;0;0;0;0;0;0})

Thank you TMS, but when I use this formula I get $0. I copied the second formula to see what is being totaled for each invoice and it returned the correct invoice numbers but all of the values are 0 which I'm assuming is why the end result I get is $0. Any further thoughts on why I returned a $0?

Thank you all for trying to help. I'm really not sure why all of the suggested formulas returned zero. I've attached the example data with everyone's formulas in it so maybe someone can help me understand why they all return zero!! Thank you again!

Thank you TMS!! I was actually in the process of copying and pasting values only to try the suggestions again. I will update once I get it to work! Sometimes it's the simplest mistakes that cause the biggest headaches!

Thank you ExcelLogan. This is the only formula that updates the same as subtotal would when rows are hidden! I'm not familiar with Map and Lambda, but I look forward to further investigating the functions as they work perfectly for my problem!! Thanks again!

=SUM(IF(ISNUMBER(XMATCH(B3:B39,FILTER(B3:B39,F3:F39="Not Loaded"))),MAP(G3:G39,LAMBDA(g,SUBTOTAL(9,g))),))

@TMS AND @shainaday88 Get 0, John Topley POST # 9 has already indicated that the reference position is misplaced, and the formula is not a problem. Provide the old method again

Formula:

Please Login or Register  to view this content.