Help searching in pivot table

New Contributor
Hello,

I would need help with the following table, I will try to explain the clearest way possible, because atm I cannot post an actual example.

So I have a list of clients (col A, client account number), type of orders (col B: buy or sell values), product name (col C: text) and volume (col D: numbers).

The client account number appears as many times as that client made an order and evidently products are repeated as well, as many products were purchased by different clients.m or same product was bought and sold by the same client several times.

Small example with one client, but imagine this is with thousands of clients and orders and products.

0001 buy Apple 3
0001 sell Apple 1
0001 sell Apple 1
0001 buy Pear 1
0001 buy Pear 1
0001 sell Pear 2
0001 buy Cherry 5

Now, I am making some other calculations based on these values in the next column E and I want to ignore from the calculations the lowest side of the order, e.g:
Client 0001 bought 3 Apples and sold 2, so I want to return 0 in column E for all the sell orders of Apples of the client 0001 (so in E2 and E3 I want 0 and <calculations> in E1, because is a buy order).

For Pears, the client 0001 bought 2 and sold 2, so buy and sell side is the same, so I want to calculate just for the buy orders, for the sell I want 0.

For Cherries, <calculate>, since there are no sell orders.

I tried to make a pivot table so I can have the orders aggregated per client, product and get the total sum of buy and sell orders, so I can see which side is higher, but then I do not know how to ignore those orders in my calculations in the original table, colum E.

I was thinking that (without pivot table), to calculate in my original table, column E, which is the highest, with sumif (something like: if the order type is sell and sumif(sell for this client, this product) < sumif(buy for this client, this product), then 0; otherwise <calculate>; otherwise (if is buy), then... the same, but for buy and with <=, to get the equal scenarios as well.

This table is very big, so it would make a lot of calculations this way, do you have a more efficient solution?

Thank you in advance and hope my explanation was good enough.
1 Reply

I honestly didn't understand what's the goal you want to achieve.

 

If you just want to ignore sells, just use an IF function: IF(type of order="sell";0;volume number).

 

Then you can sum all buy orders...

 

If you want to calculate the buy orders ONLY if volume buy > volume sell, then you have to add a new worksheet with the client stated only 1 time and then SUMIF buy and SUMIF sell and do the math.

 

Else, if I don't understand at all what you need, please explain me with some more examples... sorry