pivot table with values variable for each item

[#1]

hi all

i have a main source in which i have something like this:

salesman     product               value

john              product1              20

adrian           product1              30

mary             product1             40

john              product2             25

adrian           product2             35

mary             product2             45

and another source in which i linked salesman with a corespondent type of product

salesman       vproduct

john               product1

adrian            product2

mary              product2

and i want to create a pivot table with all salesmen on rows and with an expression like a sum of value but just for products from the second source for each salesman (like a vlookup in excel)

the result is like this:

salesman    value

john             20

adrian          35

mary           45

thanks a lot!

[Gysbert_Wassenaar · Accepted solution]

Use salesman as dimension and sum(if(vproduct=product,value)) as expression

Or have a look at the attached example for another solution.

[#3]

the formula is ok; i put also a set analysis before 'if 'and it works (another filter)

many thanks