Sum & Count on new table

Hi i need help on shaping my data but i couldnt find a proper subject to visualize it. Hopefully i can explain here better.

I have 1 table contain inventory number & 3 different table indicate type of transaction.

This 3 table have transaction number with inventory number & quantity, this table have repeating inventory number with same transaction number indicate more detail in the product but its in another table.

And i have another 3 table contain unique transaction number & multiple time record.

What i want to do is first to have a new table having the master inventory code as column1 & 3 different type of transaction as column2 column3 column4 showing the count of how many time the inventory number have been use similar to countif in excel. And column5 column6 column7 to show the cumulative quantity of the inventory number similar to sumif in excel.

Really appreciate your help,

Thanks