Sum values depending other column

Question

I have 3 excel sheets. In Sheet 1 I have 3 columns:

• order number
• agent And
• order value.

In the Sheet 2 I have only 1 column with order numbers. In the third sheet I have 2 columns:

• agent and
• total orders values.

I want to sum Order values for every Agent (on sheet 3), BUT only if order numbers from sheet 1 contains order number from sheet 2. What function should I use? Is there some function like "Contains" in excel? I tried countif but I cannot write it in one function..

Answer 1

You need to adjust your data architecture.

On Sheet1 add a column with a formula that checks if the current row's order number exists in Sheet2, like

=IF(ISNUMBER(MATCH(A2,Sheet2!A:A,0)),"include","exclude")

Now you can build a pivot table in Sheet3 based on the data in Sheet1. Drag the Agent into the rows, the order value into the Values and the sheet2Order into the Filters. Set that filter to "include".