+ Reply to Thread
Results 1 to 5 of 5

(attachment) sum number on date if 3 months preceding (array?)

  1. #1
    Registered User
    Join Date
    05-19-2014
    MS-Off Ver
    Office 2014
    Posts
    13

    (attachment) sum number on date if 3 months preceding (array?)

    Hello!

    I think this can be done with formulas, but maybe a pivot table or vb would be better.

    For the first value in a row, I need to sumif all the values which a) Match the date of the ouput column, and b) have >=3 preceding months.


    if you take a look at the attachment:

    F3 in green sums colB where colC=F2. It only does so for PersonA and PersonB because E3 is on or after their 3rd month.
    G4 applies the same logic, adding PersonC to the mix. (note that PersonA and PersonB are included, since they also meet the definition of 3rd month or later).
    and so on

    I'm open to suggestions; I can't think of the most efficient way to do this
    Attached Files Attached Files
    Last edited by Apexeon; 08-15-2016 at 05:19 PM.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: (attachment) sum number on date if 3 months preceding (array?)

    I think this does what you want. Try array-entering this formula in F3 filling down and across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Dave

  3. #3
    Registered User
    Join Date
    05-19-2014
    MS-Off Ver
    Office 2014
    Posts
    13

    Re: (attachment) sum number on date if 3 months preceding (array?)

    Actually that's exactly it. However, my data includes blanks and the formula returns blank when i blank a month (see highlighted in red).

    Also, I added PersonD with a huge number to illustrate that although it meets the 3 month rule, it should not be included in the same group as PersonC. Phrased a different way, I'm trying to answer the following analogous question:

    How many people born on or before July made a purchase in September? Now for this group, tell me how many made a purchase in October and so on.

    The tricky part I can't seem to isolate the group using formulas because I'll have someone born in July, who didn't make a purchase in September but then shows up in October because they did make a purchase then. Instead I would want them to be in a new group/row... Every formula on the adjacent right cell needs to be a function of what's in the first leftmost cell of that row, which your formula appears to do.
    Attached Files Attached Files

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: (attachment) sum number on date if 3 months preceding (array?)

    Before I look at the latest upload it occurs to me that my formula was much too complicated. Here's a simpler array-entered formula.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I see what I can do about the rest.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: (attachment) sum number on date if 3 months preceding (array?)

    Quote Originally Posted by Apexeon View Post
    ................................................
    Also, I added PersonD with a huge number to illustrate that although it meets the 3 month rule, it should not be included in the same group as PersonC. ......................................................
    .......................................
    Apexeon the latest upload doesn't reflect this description. Is this the right workbook?

    Also how many Persons A,B,C,D .... n do you expect .... the last being Person"X"?
    Last edited by FlameRetired; 08-22-2016 at 10:34 PM.

+ 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: 07-25-2015, 11:39 AM
  2. [SOLVED] How to automatically list months preceding a certain month
    By ramatthews in forum Excel General
    Replies: 5
    Last Post: 05-10-2015, 06:17 PM
  3. Adding a number of months to a start date to obtain an 'end date'
    By SamURW in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2014, 07:19 PM
  4. [SOLVED] How to add number of months to date?
    By vicstone in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-11-2014, 07:17 PM
  5. [SOLVED] Formula to insert text in new column if preceding more than one of the preceding have val
    By aimeecrystalaid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-04-2013, 11:47 PM
  6. [SOLVED] Calculate date based on number of weeks or months after start date
    By wotsup in forum Excel General
    Replies: 3
    Last Post: 08-17-2012, 06:00 AM
  7. number of months from one date to another
    By kengaroo in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-29-2010, 11:36 AM

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