I have these three tables as shown in this image below: SalesOrderTable, OrderAdjustmentTable, and TotalSalesTable.
Each order number in the SalesOrderTable represent an order number for a product from the same invoice. It has A,B, or C if an invoice is made for more than one product, but not if it is only made for one product. E.g.: Order 1703 has two products, so it has A and B at the end of each order number. Order 1704 has an order for Apple only, so it doesn’t have any letter at its end.
Should there be any adjustments for each order number, they are inputted manually in the adjustment columns.
In this table, all amount of total sales and adjustments for the same order number are totaled. So order for 1705 are combinations of 1705A, 1705B, 1705C.
Here are my current formulas, all of them produce error messages or don’t calculate correctly.
Order total amount column:
=SUMIF(SalesOrderTable[Order Number], LEFT(G4,LEN(SalesOrderTable[Order Number])-1),SalesOrderTable[Order Sales Amount])
Order Total Adjustment Column:
=sumifs(OrderAdjustmentTable[#All],OrderAdjustmentTable[Order Number],MATCH(B19,LEFT(SalesOrderTable[Order Number],LEN(SalesOrderTable[Order Number])-1), 0), "*Adjustment",OrderAdjustmentTable[#All])
I know it may have something to do with match and sumifs, but all the formulas I made led to error messages. Anybody can help me with what I did wrong with these formulas above, and how do I fix them? I am at my wits end here and will appreciate any advice given. Thanks a lot!
What I want to achieve is to fill:
The order total amount column in TotalSalesTable with all orders that have the same order number, irrespective of their ABC. So Total amount for 1705 will be the sum total of order amount for 1705A, 1705B, 1705C.
For the order total adjustment, I want to have all the adjustments value for the order number column in TotalSalesTable from OrderAdjustmentTable, irrespective of their ABC. So total adjustments for 1705 in TotalSalesTable will be 1705B and 1705C.