Hello and thanks in advance for your help.
I am creating a sales & commission tracker. I am using SUMPRODUCT in order to be able to track on one sheet (tab) by pay period, while allowing for a summary tracking of sales by month on another sheet (tab),
I have been able to create the proper SUMPRODUCT formula. The formula works, but I would like the ability to change the year that is included in the date range. I would like to be able to enter that year in another cell, and include that cell as a partial date criteria. Here is the formula I'm currently using:
=(SUMPRODUCT((Jan!C9:C16>=DATEVALUE("1/1/2013"))*(Jan!C9:C16<=DATEVALUE("1/31/2013")),Jan!E9:E16)
Jan!C9:C16 represent the date range. Jan!E9:E16 represent the numbers to add up if the dates are between or equal to Jan 1, 2013 and January 31, 2013
To reiterate, I would like to be able to change the year in the above formula by changing a cell. This way, I will not have to rewrite change the formula next year, or if I decide to track previous years. I've tried several combinations, and so far I have been unsuccessful. Any ideas?
Bookmarks