Search code examples
excelexcel-formulaexcel-2010worksheet-function

Excel help on combination of Index - match and sumifs?

I have these three tables as shown in this image below: SalesOrderTable, OrderAdjustmentTable, and TotalSalesTable.

enter image description here

SalesOrderTable

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.

OrderAdjustmentTable

Should there be any adjustments for each order number, they are inputted manually in the adjustment columns.

TotalSalesTable

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!

Edit:

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.

Solution

  • As long as there are no orders that overlap with and without letters (for instance, the SalesOrderTable having records for 1703, 1703A, and 1703B), the following formula should work in cell C19, from where you can copy it down:

    Order Total Amount:

    =SUMIFS(SalesOrderTable[Order Sales Amount],SalesOrderTable[Order Number],$B19)+SUMIFS(SalesOrderTable[Order Sales Amount],SalesOrderTable[Order Number],$B19&"*")

    This formula adds together the exact and approximate matches, but like I said if any orders both have and are lacking a letter something will likely get duplicated/double counted. You should also be able to replicate the formula for the Order Total Adjustment Column.