substract a positive value besed on column header How?

Hi,
this is my first post here

I am looking for help with the following.
I have a table of products and they will have IN and OUT values based on orders (copied from elsewhere). The copied values are all positive, but actually IN is positive and OUT is negative.

Product	IN1	IN2	OUT1	OUT2
A	200	100
B	30	50	5
C	50		35
A	20			35

More and more INs and OUTs will be added during the year. I have changed my source date to a TABLE so the cell names can be updated easily.

I want my pivot table to have the sum of all INs and OUTs (say, for A), while treating OUT as negative.
I could add a Calculated field with the formula In1+IN2-OUT1-OUT2, but then I will need to manually change the formula each time a new IN our OUT comes. I would love that when I add a new IN/OUT to the pivot table Value section, the SUM=IN-OUT for each row would update automatically.

Theoretically, I have tried making a separate sheet that checks if the column name has OUT in it and then multiplies the value by -1 and getting the Pivot from there. But this involves having an extra page and manually increasing the size of the TABLE on the second page.

Thank you all in advance for help.

Thanks for your answer.

Do you mean this should be added to each item in the source table (and not Pivot)? I can not convert Pivot to Table.
Capture.JPG
I would love to use Pivot table as then I can see all groups of items. Also, I assume the SUMPRODUCT needs a manual imput of all items (one formula for apples, one for water), right? As I have around 400 items already now, this will be complicated. In addition, the array you mentioned, apparently, need to be in arrays (is this the right word?) of all INs in sequence then all OUTs in sequence. I had hoped it is possible to keep them in the order they come in (e.g. based on dates).

I've attached what I am trying to get. Hope this helps.

Should have done this at the beginning, sorry.

Try it can reorganize your data, it will be a bit easier.

I would like to thank everyone for helping, but this is still not what I am looking for. Either this is more complicated than I thought or my description was not good and now you are confused.. Sorry about that.

Originally Posted by JeteMc

In the screen shot it appears that some items (bread) have all values on a single row. If it is possible to continue that trend for all items then you could assign your pivot table (see sheet 1) to reference a helper range (rows 8:13) that could be populated using: =IF(LEFT(C$9,3)="out",-C3,C3)
Let us know if you have any questions.

Sadly, it is not possible to have all in a single row due to the large amount of data and it comes in codes. For user it would be easier to just keep copying BREAD from source data than searching if such code has already been ordered or not and write in the same row.

Originally Posted by stasinek

Try it can reorganize your data, it will be a bit easier.

Thanks. Does this involve manually changing the OUT to negative?

I am attaching a dummy file with the order data from the supplier - perhaps it will be more helpful. My end goal is to know how much we still have in stock. Sheet Orders - is the most raw data I get. Actually, I get each IN/OUT in separate e-mail and separate file (since they come on different dates). The "Data" sheet - I hoped it would make it easier....

I see that you are using Ex 2016 and so you have Power Query.
I would go in this direction. Add another data to the table created by you and on the "Data" tab click "Refresh everything" - it should work.
When adding more data, remember to place them inside the table and not outside it.

I got around by reorganazing the sheet with data. Now it includes Orders horizontally, I do not have to do is copy the items from the order and as this is a structured table it will autopopulate all other rows. Including the conversion to negative.
Sadly the Pivot does not show info "per order", but this can be filtered out.

Thank you all for the ideas!