Hello all,

I hope someone can help me out. I would like to calculate the return rate for my orders for an online shop of a month (e.g. April).

However, since customers have 30 days as a return period, the returns and the orders are not comparable the same but delayed.
I have 2 raw-data spreadsheets: 1) April orders 2) Returns from April to June to catch all returns to April orders.

Both spreadsheets contain 3 columns: 1) Order number 2) SKU 3) Quantity.

Now in the third sheet ("Dashboard") I want to calculate the return rate for April.
Now I am missing the formula for column C in 'Dashboard'. For this, I need a formula that outputs the sum of the returns (column C 'Returns April-June'), but only those that are existing in the April orders.

All orders from April and all returns that refer to an order from April have the same order ID.
This order ID can be used to link/compare the orders and returns.

So again in short:
Criteria:


Sum of column C in 'Returns'.
Range: Column B 'Returns
Search criteria: A2 'Dashboard'.

But only if column A in 'Returns' and column A in 'Orders' are the same.
Attached is a sample file: Ret_ENG.xlsx

Thanks in advance!

This one was a lot more convoluted than I thought it would be, unless I've completely missed something obvious!
In the attached there are 3 solutions.
1. Formula in column C of the Dashboard sheet. This depends on the formula in cell I2 of the Returns April - June sheet.
2. Formula in column R of the Dashboard sheet which is a version of (1) but incorprates the I2 formula. This eliminates the need for the I2 formula. I'd call this a mega-formula but it could be made into a named lambda formula which would make things easier for the user.
3. A Power Query solution in cell G1 of the Dashboard sheet. I prefer this solution to the others. After you've updated the data in the table on sheet April Orders and/or the one in the Returns April - June sheet, you just need to right-click on the green table in the Dashboard sheet and choose Refresh.

Attached Files

msofficeforums51177Ret_ENG.xlsx (26.4 KB, 1 views)

this message is to trigger a notification to the OP since I updated my previous message with a solution

Hi,

I have attached a modified file containing a solution.

An extra column (column D) was added to the "Returns April - June" sheet and in that column the value "returned" was entered is each row.

also an extra column (column D) was added to the "April Orders" sheet and that column the VLOOKUP is used to call up a "returned" value from the "Returns April - June" sheet based on the Order ID. So, only orders made in April (from the April Orders sheet) will have a value of "returned" in column D. In order to make it tidy, the VLOOKUP function was nested in an IFERROR function to display "not returned" for orders that did not originate in April or were not returned by a customer, else the #N/A error will be displayed in column D.

Finally, the SUMIFS function was used to sum up the orders made in only April but returned in April - June.

Attached Files

Ret_ENG (rev1).xlsx (13.9 KB, 2 views)

Attached is a version how I would design it (on added 3 sheets).

Orders are entered into single sheet (various months, and various years). As result, you design the workbook once, and the use it for as many months or years as you need it;

Tables on sheets are designed as Defined Tables. This allows user(s) easily filter tables (e.g. to enter returns for certain orders), and whenever a new entry is entered into table, the formats and formulas are expanded to new entry so long the column contains a single format or formula, and there are no gaps left into table when the record is added;

In current example, I used dates in OrderDate column, and calculated values for month number in format yyyymm in columns OrderPeriod and ReturnPeriod. In case the exact dates don't have any value for you, you can use month numbers instead;

On Report sheet, user can select order month from data validation list, and report for all SKU's in orders from this month is displayed. I designed a single report, but you can have any number of different reports designed;

Currently the returning of specific order can be done once and in full ordered quantity (based on your data). In case return can be partial (but still one-time event), you need to add into Orders table the column for return quantity, and edit the formula in ReturnQty column on Report sheet accordingly;

When for same order there can be several returns, especially in different periods, then you need a separate sheet with separate returns table there;

Currently I designed the formula for ReturnQty on Report sheet so, that only entries with same period as the report period are accounted. You can add to report columns, where returned quantities with OrderPeriod=report period are returned month later, 2 months later, etc.

Attached Files

Ret_ENG.xlsx (22.3 KB, 1 views)