Forum Discussion
Solved
SUMPRODUCT WITH 2 CONDITIONS
Hi everybody I would really appreciated your help. I can't get the formula right 😞 Basically I want to know the average buy and sell price for BTC (and then various other tickers) from all t...
In SUMPRODUCT, you cannot filter using AND the way you did. Instead, you have to include the "filter" inside the function. So, to get the weighted average, use
=SUMPRODUCT(--(B2:B30=B33),--(A2:A30=A33),C2:C30,D2:D30)/SUMPRODUCT(--(B2:B30=B33),--(A2:A30=A33),C2:C30)The double negation converts the boolean result into numbers (1 for TRUE and 0 for FALSE).
nataleee1460 Perhaps you'll find a pivot table more suitable to this kind of summary. Please see attached.
Thank you so much for your response. I think this is a great idea but unfortunately I don't have any experience of pivot tables. I will try to look into it. One quick question. From the spreadsheet that you sent me, where is the sumproduct formula? Is that something that you set up within the pivot table? Thanks again?