Forum Discussion
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 the trades. I attach a spreadsheet.
Thank you so much for your help!
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).
5 Replies
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).
- Thank you so much for your response! That does indeed work! I've never used those hyphens before. Thank you again :))
- You are welcome.
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?
