+ Reply to Thread
Results 1 to 3 of 3

Sum Values Linked to 1) a Date AND, 2) Another Date X Days after that Date

  1. #1
    Registered User
    Join Date
    04-16-2014
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Sum Values Linked to 1) a Date AND, 2) Another Date X Days after that Date

    (Posted this earlier but hoping to simplify):

    I'm trying to sum revenue listed in a table using a SUMIFs function filtering for two separate dates. One is the "Install Date" ('Revenue Data'!D:D) and the other is the "Date of Spend" ('Revenue Data'!B:B), which must be a certain number of days *after* the install date in the same row.

    I need to filter and sum Revenue ('Revenue Data'!C:C) by the following - 1) The Install Date (D:D) being between two dates listed in the first worksheet, and 2) Date of Spend (B:B) being a certain number of days beyond the Install Date. (I am also filtering by the 'Network' in $A$3, but that's beside the question.)

    This is the formula I'm using, as seen in Sheet1, cell C6. The problem comes at the end, when trying to sum all revenue from Dates of Spend that are (A6-1) days past the Install Date.

    =SUMIFS('Revenue Data'!C:C,'Revenue Data'!A:A,$A$3,'Revenue Data'!D:D,">="&+$B$1,'Revenue Data'!D:D,"<="&+$B$2,'Revenue Data'!B:B,'Revenue Data'!D:D+A6-1)

    or --> SUMIFS(Revenue, Source Network = Text in cell A3, Install Date is greater than date in B1 and less than date in B2, Date of Spend = Install date + a number of days listed in Column A).

    So in a case where A6 at the end equals 2, it should return all revenue from users that 1) installed on any day within the B1-B2 range, and 2) paid something on the day after installing.

    Help appreciated!!!!!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-16-2014
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Sum Values Linked to 1) a Date AND, 2) Another Date X Days after that Date

    Hey guys - anyone know how I could solve this?

  3. #3
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Sum Values Linked to 1) a Date AND, 2) Another Date X Days after that Date

    Hello

    Try this.

    =SUMPRODUCT('Revenue Data'!C$2:C$130,('Revenue Data'!A$2:A$130=A$3)*('Revenue Data'!D$2:D$130>=B$1)*('Revenue Data'!D$2:D$130<=B$2)*('Revenue Data'!B$2:B$130='Revenue Data'!D$2:D$130+A6-1))

    DO NOT use whole column reference use dynamic range instead.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

+ 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. Replies: 5
    Last Post: 01-16-2014, 11:51 AM
  2. Calculate End Date Using Start Date and No. of Days excluding specific days.
    By SinusxCosinusx in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2013, 05:48 AM
  3. [SOLVED] Need average of days for date values from date to current day that updates current date
    By FinGhost in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-12-2013, 04:41 PM
  4. [SOLVED] Lookup Date 14 days prior and add multiple values for that date into column
    By Shylmysten in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-03-2013, 02:15 AM
  5. excel formula: days remaining=end date-todays date+extention days
    By fsprings in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 01-04-2013, 06:45 AM

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