Hello,
I am attempting to convert a SUMIFS formula to a formula that performs that exact same look up and calculations, but multiplies the returned values instead of adding them. The current formula is:
=SUMIFS(INDEX('Daily Total Return Data'!$B$2:$GI$2915,,MATCH($A4,'Daily Total Return Data'!$B$1:$GI$1,0)),'Daily Total Return Data'!$A$2:$A$2915,">="&$B4,'Daily Total Return Data'!$A$2:$A$2915,"<="&$C4)
I have attached a sample sheet with all most of the data cleared out.
I assume you want to ignore any zero values otherwise the PRODUCT will be zero, so try this "array formula"
=PRODUCT(IF('Daily Total Return Data'!$A$2:$A$2915>=$B4,IF('Daily Total Return Data'!$A$2:$A$2915<=$C4,IF(INDEX('Daily Total Return Data'!$B$2:$GI$2915,,MATCH($A4,'Daily Total Return Data'!$B$1:$GI$1,0))<>0,INDEX('Daily Total Return Data'!$B$2:$GI$2915,,MATCH($A4,'Daily Total Return Data'!$B$1:$GI$1,0))))))
confirmed with CTRL+SHIFT+ENTER
...or this version is shorter but may be slower.....
=PRODUCT(IF('Daily Total Return Data'!$A$2:$A$2915>=$B4,IF('Daily Total Return Data'!$A$2:$A$2915<=$C4,IF('Daily Total Return Data'!$B$1:$GI$1=$A4,IF('Daily Total Return Data'!$B$2:$GI$2915<>0,'Daily Total Return Data'!$B$2:$GI$2915)))))
Last edited by daddylonglegs; 08-21-2014 at 03:52 PM.
Audere est facere
That did it! Thanks for the assistance.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks