Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2015
    Posts
    11
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Add Date Range to SUMPRODUCT Formula

    I trying to modify an existing formula, but I’ve found it to be harder than I anticipated. I hope someone on this forum can help me. The formula is:

    =SUMPRODUCT($E$2:$E$11,H4,--($C$2:$C11="RED"),--($D$2:$D$11="CCA"))

    I would like to add additional criteria for a dates range between Jan 1 and Jun 30 of the current year (Column B). So the formula would total the Points if the Dates are between 01/01 (current year) and 06/30 (current year) and the Team = “RED” and the Area = “CCA”.

    Excel.jpg


    Thank you.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Welcome to the Lounge.

    ..have you thought about using the SUMIFS formula??

    zeddy
    •Excel Protocol Engineer
    .

  3. The Following User Says Thank You to zeddy For This Useful Post:

    JAATR (2015-08-03)

  4. #3
    New Lounger
    Join Date
    Aug 2015
    Posts
    11
    Thanks
    6
    Thanked 0 Times in 0 Posts
    zeddy,

    Thank you for the suggestions; I used the following formula and got the results I was looking for:

    =SUMIFS($E2:$E$11,$B$2:$B$11,">=1/1/15",$B$2:$B$11,"<=06/30/2015",$C$2:$C$11,"=RED", $D$2:$D$11,"CCA")

    One other questions concerning the date range (year). I will reuse the worksheet each year, so I will need to adjust/update the formula according (e.g., replace 2015 with 2016 next year). Is it possible to substitute something for 2015 so that I don't have to modify the formula every year?

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jaatr,

    Just place the start end dates in cells, name the cells, then use the names in the formula.
    Example1.JPG

    An alternative is to create Names that contain the dates then you just change the Name definitions each year, and as previous use the names in the formula.
    Example2.JPG

    Names in Workbook:
    Example3.JPG

    Note: BegPeriod and EndPeriod were entered as 1/1/2015 & 6/30/2015 respectively in the name definition box and converted by Excel to their respective date numbers.

    Test File: NamedValues.xlsx

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    JAATR (2015-08-03)

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Try:
    =SUMPRODUCT(($E$2:$E$11)*(($C$2:$C11="RED")*($D$2: $D$11="CCA")*(B2:B11>DATE(2015,1,1))*(B2:B11<DATE( 2015,6,1))))

    Maud

  8. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Modified so the year will allways be the current year without having to change it:

    =SUMPRODUCT(($E$2:$E$11)*(($C$2:$C11="RED")*($D$2: $D$11="CCA")*(B2:B11>DATE(YEAR(TODAY()),1,1))*(B2: B11<DATE(YEAR(TODAY()),6,1))))

Posting Permissions

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