I'd like to perform the product function for cells in the rows that contain the year 2016. I tried the following code but it just returns FALSE. My thinking: if the year of the date in column A contains "2016", then multiply the corresponding cells in column A for these rows. Any suggestions?
Thanks!
Please Login or Register to view this content.
Hello
Try using SUMPRODUCT, for example:
Formula:
Please Login or Register to view this content.
This assumes that column A is actual dates and not text.
DBY
One way...
A1 = 1/1/2016
B1 = 12/31/2016
=SUMIFS(C25:C336,A25:A336,">="&A1,A25:A336,"<="&B1)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
@DBY, That formula works great. The only thing is that I need to use the product(range+1)-1) formula to deal with the return series. Is there a way to incorporate this?
Also it might be the most efficient to extract just the year as I'm dealing with a steam of returns going back to the 90s and the spreadsheet is data intensive as is so trying to avoid extra columns.
Last edited by TIFinance; 06-08-2016 at 02:24 PM.
Try
=IF(YEAR(A25:A336)=2016,PRODUCT(C$25:C$336)-1)
Enter with Ctrl+Shift+Enter
@John, this is returning "false" which may be because I have dates going back further than 2016.
The formula only extracts data for 2016 so other data is not relevant. You will get FALSE if there is no data for 2016.
True, however, the "false" is overriding the "true". Since there is at least one "false" in the array, the entire formula isn't returning a product for any of the 2016 data. I'm currently returning a "false" until I adjust the range to only include rows where there are 2016 dates.
So, I guess I misunderstood what you wanted?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks