Combine SUMPRODUCT with SUMIF
I am trying to write a formula to combine sumproduct and sumif to get weighted average price that is calculated based on dates selected.
Is it possible?
Thanks...
Hello & Welcome to the Board,
Try this...
C31 =AVERAGEIFS($B$2:$B$26,$A$2:$A$26,">="&A31,$A$2:$A$26,"<="&B31)
C32 =AVERAGEIFS($B$2:$B$26,$A$2:$A$26,">="&A32)
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
Hello sahjid and welcome to the forum,
See if the attached pivot table doesn't get what you want.
One test is worth a thousand opinions.
Click the * below to say thanks.
averageifs is not in excel 2003
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
You are correct, but the attachment is 2007.
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
If you want volume-weighted price, I think ...
=SUMPRODUCT(($A$5:$A$26>=A31) * ($A$5:$A$26<=B31), $B$5:$B$26, $C$5:$C$26) / SUMPRODUCT(($A$5:$A$26>=A31) * ($A$5:$A$26<=B31), $C$5:$C$26)
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks