Hi everyone,
I have a number of transactions in Excel listed in columns A-C (security, quantity and date). Based on specific dates and security names in columns G and H, I was trying to figure out a way to sum up all the transaction for a given name that happened up to (and including) that date. I am using this formula, but the results I got seem to wrong.
SUMPRODUCT(--(A$1:A$100=H$5),--(C$1:C$100>=G$5),--(B$1:B$100))
If anyone could please take a look and let me know where things went wrong, it would be a huge help.
Your current calculation is summing values >= date specified rather than <= date specified which is I suspect what you want.
Other notes re: existing SUMPRODUCT:
The coercion of the value range (B1:B100) is not necessary and indeed negates one of the main advantages of double unary on the boolean arrays
Your use of $ is I think the wrong way around if you intend to apply the formula in J5 to remaining rows
Given all of the above I'd suggest:
If using XL2007 or above (exclusively) then revert to SUMIFS which is significantly more efficient than SUMPRODUCTJ5: =SUMPRODUCT(--($A$1:$A$100=$H5),--($C$1:$C$100<=$G5),$B$1:$B$100) copied down
J5: =SUMIFS($B$1:$B$100,$A$1:$A$100,$H5,$C$1:$C$100,"<="&$G5) copied down
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thank you very much! Both formulas work for me, but we're in the middle of upgrading from Excel 2003 to 2007 at work, so I think I'll keep using sumproduct, even if it is more inefficient.
Thanks again for the prompt reply.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks