+ Reply to Thread
Results 1 to 2 of 2

SUMPRODUCT with Variable Year

  1. #1
    Registered User
    Join Date
    08-21-2013
    Location
    Texas, United States
    MS-Off Ver
    Excel 2010
    Posts
    1

    SUMPRODUCT with Variable Year

    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?

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: SUMPRODUCT with Variable Year

    Let's say you have the year in A1.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] SUMPRODUCT, OR, Less than Month & Less than Year
    By JUGGAKNOTZZ in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 03-01-2013, 10:51 AM
  2. Replies: 3
    Last Post: 07-05-2012, 01:49 AM
  3. Sumproduct with month and year ???
    By jpeirano in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2011, 09:07 AM
  4. Sumproduct for year totals
    By wnstar21 in forum Excel General
    Replies: 2
    Last Post: 09-08-2009, 04:03 PM
  5. sumproduct in a given year
    By Bumblebee in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-19-2006, 02:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1