From a sheet of mixed assets, calculate average cost of asset based on asset type

New Contributor

I am trying to simplify a process that currently requires creating sheets for each asset type.


Eg. From the following table I want to find the AVG Purchase Rate

1AssetAmount PurchasedPurchase RateValueEvent





As I am trying to place the formulas on a Summary sheet and the data on the Transactions sheet my attempt was


Where Summary B3 = ABC

=(IF(Transactions!A:A = Summary!$B3,((Transactions!B:B)*(Transactions!C:C))/(SUM(Transactions!B:B)),""))

but I got a spill error


I also tried 


which seems like it might work, but I'm not 100% sure if there is a better way


I also tried to combine the variables using IF And

=IF(AND(Transactions!$A$2:$A$400,Summary!$B3,Transactions!$E$2:$E$400,"BUY"), SUM(Transactions!$D$2:$D$400)/SUM(Transactions!$B$2:$B$400))

which totally failed. Is grouping variables like this even possible?


Any thoughts or recommendations on how to proceed?

2 Replies
best response confirmed by Fridley (New Contributor)

If I understood this correct:
=UNIQUE(FILTER(A:A,TRUE,"")) will return a list of unique "Asset" and then you can average if off of that:
(presuming the above filter is in H) - you will need to copy down to match the spill from the filter, but should do what you're after.


example workbook 

Have not tried incorporating "Unique" before, but shall give a it a go.


Currently following using the UNIQUE for my 3 sets of unique data (Asset/User/TransactionType) I have created a sumif




and a averageif 



Seems to have done the trick. 


P.S. Trans A ="USER"; Trans D = "TransactionType"; Trans E = "Asset"; Trans F = "Amount"; Trans I = "Rate" ; Trans K = "Value"