sumif index match formula and sumproduct index match formula help

[arnakke]

	A	B	C	D
1	Company	Units	Price	date
2	X	100	10	20/04/2017 <tbody> </tbody>
3	X	100	20	20/04/2017 <tbody> </tbody>
4	X	100	15	20/04/2017 <tbody> </tbody>
5	Z	150	100	20/04/2017 <tbody> </tbody>
6	Z	150	200	20/04/2017 <tbody> </tbody>
7	X	100	17	21/04/2017 <tbody> </tbody>

<tbody>
</tbody>

Hello,
This is a very simplified table, i have thousands of trades and more than hundred companies with multiple trades of the same company in any one given day. I am struggling to come up with a formula which looks up the company name and the date and aggregates the total amount of shares purchased (units) references the date and company name. Then I also need to get the average price which i would use a sumproduct formula (units x price) divided with sum of the units but some how need to incorporate index match for look up.

In another sheet i would have the aggregated data.

Company	X	X	Z	Z
date	units	price	units	price
20/04/2017
21/04/2017
22/04/2017
23/04/2017

<tbody>
</tbody>

Help to solve this problem would be much appreciated.
Thx up front.

 

[arnakke]

I would like to add, its a big spreadsheet so I also need a solution which does not break the spreadsheet ie the most efficient formula for processing power. I just read a thread about sum-product would slow down the calculation significantly????

Thanks again

 

[DHayes]

Hi,
This would be the simplest formula.

=SUMIFS(Sheet1!B$2:B$7,Sheet1!$A$2:$A$7,B$1,Sheet1!$D$2:$D$7,$A3)

Copy down and across

 

[DHayes]

Sorry slight error in previous formula.
Try this one

=IF(B$2="units",SUMIFS(Sheet1!$B$2:$B$7,Sheet1!$A$2:$A$7,B$1,Sheet1!$D$2:$D$7,$A3),SUMIFS(Sheet1!$B$2:$B$7,Sheet1!$A$2:$A$7,B$1,Sheet1!$D$2:$D$7,$A3))

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​
1​	Company	Units	Price	date		Sheet1
2​	X	100​	10​	20/04/2017
3​	X	100​	20​	20/04/2017
4​	X	100​	15​	20/04/2017
5​	Z	150​	100​	20/04/2017
6​	Z	150​	200​	20/04/2017
7​	X	100​	17​	21/04/2017

<tbody>
</tbody>

	A​	B​	C​	D​	E​	F​	G​
1​	Company	X	X	Z	Z		sheet2
2​	date	units	price	units	price
3​	20/04/2017	300​	45​	300​	300​
4​	21/04/2017	100​	17​	0​	0​
5​	22/04/2017	0​	0​	0​	0​
6​	23/04/2017	0​	0​	0​	0​

<tbody>
</tbody>

Sheet2

B3 =SUMPRODUCT((Sheet1!$D$2:$D$7=Sheet2!$A3)*(Sheet1!$A$2:$A$7=Sheet2!B$1)*(Sheet1!$B$1:$C$1=Sheet2!B$2)*(Sheet1!$B$2:$C$7)) COPY ACROSS AND DOWN

 

[arnakke]

Thanks a lot MarzioTullio and Dhayes works very well.

sorry for not being clear on my second question.

The price cell will be the sumproduct of units and price for a given company on each date. Example company X on 20/04 (100x10+100x20+100x15)=4500 which is the answer but how do i create a formula which provides this answer for company x on date 20/04

Hope this is clear.

Really grateful,
M