Getting Total QTY in Two Columns and QTY Based on Location Date Range: A Tech Support Solution

Getting Total QTY Two Columns QTY Based on Location & Date Range: Tech Support Solution

In this article, we will discuss how to get the total QTY of two columns based on location and date range. This is a common problem faced by many users and can be solved using Excel functions. In this case, the user tried using a slew of functions like COUNTIFS and SUMIFS but was not successful. We will explore an alternative solution using two tables and explain how to implement it.

Problem Statement:

The user wants to get the total QTY of two columns based on location and date range. The data is organized in two tables: Table 1 contains the location and date, while Table 2 contains the QTY. The user wants to get the total QTY for each location and date range.

Solution:

To solve this problem, we can use the SUMIFS function in Excel. However, in this case, the user reported that the SUMIFS function was not working as expected. Therefore, we will explore an alternative solution using two tables.

Step 1: Create a Pivot Table

The first step is to create a pivot table based on Table 1. The pivot table should have the location and date range as the rows and the count of records as the values. To create a pivot table, follow these steps:

1. Select the data in Table 1.
2. Go to the Insert tab and click on PivotTable.
3. In the Create PivotTable dialog box, select New Worksheet as the location for the pivot table.
4. Drag the location and date range fields to the Rows area of the pivot table.
5. Drag the count of records field to the Values area of the pivot table.

Step 2: Create a Table for QTY

The second step is to create a table for QTY based on Table 2. The table should have the location and date range as the columns and the QTY as the values. To create a table, follow these steps:

1. Select the data in Table 2.
2. Go to the Insert tab and click on Table.
3. In the Create Table dialog box, check the box that says My table has headers.
4. Drag the location and date range fields to the Columns area of the table.
5. Drag the QTY field to the Values area of the table.

Step 3: Combine the Two Tables

The final step is to combine the two tables. To do this, we can use the VLOOKUP function in Excel. The VLOOKUP function will look up the location and date range in the pivot table and return the corresponding QTY from the table for QTY. To combine the two tables, follow these steps:

1. Create a new column in the pivot table for QTY.
2. In the first cell of the QTY column, enter the following formula:

This formula will look up the location and date range in Table 2 and return the corresponding QTY. The formula uses the CONCATENATE function to combine the location and date range into a single value, which is used as the lookup value. The MATCH function is used to find the column that contains the QTY value. The FALSE argument ensures that an exact match is required.

In this article, we discussed how to get the total QTY of two columns based on location and date range. We explored an alternative solution using two tables and explained how to implement it. By using a pivot table and a table for QTY, we were able to combine the two tables and get the desired results. This solution can be used in a variety of situations where you need to get the total QTY based on multiple criteria.

References

• Create a PivotTable to analyze worksheet data
• Create a table from structured data
• VLOOKUP function