How to Combine VLOOKUP with SUMIF in Microsoft Excel - Office 365

Engineering - Follow us to learn every day 💡

❤️ Learning & Engineering

Published Apr 5, 2024

Combining VLOOKUP with SUMIF in Microsoft Excel is a powerful technique for data analysis, allowing you to retrieve specific data based on a condition and then aggregate values associated with that data. This approach is particularly useful in scenarios involving financial reports, inventory management, and performance tracking, where you need to sum values based on a matching criterion from a different dataset or within the same dataset.

👉Purchase our book to improve your Excel productivity

Benefits

Integrating VLOOKUP with SUMIF enables you to:

- Enhance Data Analysis: Efficiently analyze large datasets by combining lookup and conditional summing capabilities.

- Improve Financial Reporting: Accurately calculate total expenditures, revenues, or other financial metrics for specific items or categories.

- Streamline Inventory and Sales Management: Quickly sum quantities or sales figures for specific products or services identified through lookup functions.

Step-by-Step Guide

Step 1: Prepare Your Datasets

Assume you have two tables:

1. Product Table: Lists product IDs and names.

- Column A: "Product ID"

- Column B: "Product Name"

2. Sales Table: Records sales transactions, including product ID and sales amounts.

- Column D: "Product ID"

- Column E: "Sales Amount"

Step 2: Use VLOOKUP to Retrieve Product Names

1. Add a Header for Product Names in the Sales Table: In cell F1 of the Sales Table, type "Product Name".

2. Retrieve Product Names: In cell F2, enter the VLOOKUP formula to fetch the product name based on the product ID from the Product Table:

=VLOOKUP(D2, A:B, 2, FALSE)

3. Copy the Formula Down: Drag the fill handle from F2 down to apply the formula to all sales entries.

Step 3: Use SUMIF to Aggregate Sales by Product

1. Prepare an Aggregation Table:

- In a new section or sheet, prepare a table with unique product names listed in Column H and a header "Total Sales" in Column I.

2. Sum Sales for Each Product: In cell I2, enter the SUMIF formula to sum sales amounts for the product name listed in H2:

=SUMIF(F:F, H2, E:E)

3. Copy the Formula Down: Apply the formula to all products listed in your aggregation table.

👉Purchase our book to improve your Excel productivity

Example

Scenario: Regional Sales Analysis for Multiple Products

Imagine you're an analyst at a company selling various products across different regions. You have a product catalog listing each product's ID and name, and a sales log recording sales transactions, including product ID, sales amount, and region. Your task is to analyze total sales for each product by region.

Step 1: Prepare Your Data Sheets

1. Product Catalog Sheet:

- Column A: "Product ID"

- Column B: "Product Name"

- Products listed from A2:B5, for example, "P001" to "P004".

2. Sales Log Sheet:

- Column A: "Transaction ID"

- Column B: "Product ID"

- Column C: "Region"

- Column D: "Sales Amount"

- Sales transactions listed from A2:D20.

Step 2: Add Product Names to Sales Log

1. Extend Sales Log Table:

- In the Sales Log sheet, add a header in E1: "Product Name".

2. Retrieve Product Names Using VLOOKUP:

- In E2, input the formula to fetch product names based on IDs in column B:

Recommended by LinkedIn

How to Convert a Date into a Month and Year in…

Engineering - Follow us to learn every day 💡 8 months ago

How to Hide Zero Values in Microsoft Excel - Office…

Engineering - Follow us to learn every day 💡 9 months ago

Adding a Total Row in Microsoft Excel- Office 365

Engineering - Follow us to learn every day 💡 7 months ago

=VLOOKUP(B2, ProductCatalog!A:B, 2, FALSE)

- Drag the formula down through E20.

Step 3: Sum Sales by Product and Region

1. Create an Analysis Table:

- In a new sheet or section, set up a table with "Product Name" in column G and "Region" in column H. In column I, add the header "Total Sales".

2. List Unique Combinations:

- In columns G and H, list unique combinations of product names and regions for which you want to calculate total sales.

3. Use SUMIF to Aggregate Sales:

- In I2, enter the formula to sum sales for the first product-region combination:

=SUMIF(SalesLog!E:E, G2, SalesLog!D:D)

- Modify the formula to conditionally sum based on both product and region by using SUMIFS:

=SUMIFS(SalesLog!D:D, SalesLog!E:E, G2, SalesLog!C:C, H2)

- Drag down to cover all product-region combinations.

Data

- Product Catalog:

- A2: "P001", B2: "Widget"

- A3: "P002", B3: "Gadget"

- Sales Log:

- Transactions include both "P001" and "P002" sold in regions "North" and "South".

After applying VLOOKUP in the Sales Log, each transaction now includes the product name. Using SUMIFS in your analysis table, you calculate total sales for "Widget" and "Gadget" in both "North" and "South" regions.

Advanced Tips

- Optimizing for Performance: If dealing with very large datasets, consider using Excel's Table feature (`Ctrl + T`) for dynamic range management and improved calculation speed.

- Handling Errors: Incorporate IFERROR with your VLOOKUP to handle possible N/A errors, making your formula more robust:

=IFERROR(VLOOKUP(D2, A:B, 2, FALSE), "Product Not Found")

- Expanding to Multiple Conditions: For more complex conditions, explore combining SUMIFS with VLOOKUP or using Excel's INDEX and MATCH functions for more flexibility in lookup and aggregation criteria.

👉Purchase our book to improve your Excel productivity :