+ Reply to Thread
Results 1 to 8 of 8

SUMIF: Need formula to use month to determine sum_range

  1. #1
    Registered User
    Join Date
    01-09-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2003
    Posts
    24

    SUMIF: Need formula to use month to determine sum_range

    So I need the sum_range part of the function to reference a column depending on the month. So, for example, I want a formula that will make the sum_range part of a SUMIF equal column L (12th column) if cell A1 is 12/31/2013 (the 12th month).

    Thanks!

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: SUMIF: Need formula to use month to determine sum_range

    there are a couple of ways to do it. Yet it would be helpful if you assemble a sample and the desired result

  3. #3
    Registered User
    Join Date
    01-09-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: SUMIF: Need formula to use month to determine sum_range

    Hmm I am having trouble uploading a table or image but I'll do my best. Say the date in A1 is 6/30/2013 and I am doing a formula on the below data set:

    A 1 2 3 4 5
    A 10 11 12 13 14
    B 20 21 22 23 24
    D 30 31 32 33 34
    D 40 41 42 43 44

    I would want SUMIF(A:A,"D", ? ) to equal 78. If the date in A1 were 5/31/2013, I would want the result to be 76, etc.
    Attached Images Attached Images

  4. #4
    Registered User
    Join Date
    01-09-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: SUMIF: Need formula to use month to determine sum_range

    Please ignore the attached image

  5. #5
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: SUMIF: Need formula to use month to determine sum_range

    could you attach an excel file with the data?

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMIF: Need formula to use month to determine sum_range

    Try this...

    Data Range
    A
    B
    C
    D
    E
    F
    1
    5/31/2013
    76
    2
    -----------
    -----------
    -----------
    -----------
    -----------
    -----------
    3
    A
    1
    2
    3
    4
    5
    4
    A
    10
    11
    12
    13
    14
    5
    B
    20
    21
    22
    23
    24
    6
    D
    30
    31
    32
    33
    34
    7
    D
    40
    41
    42
    43
    44


    Formula in B1:

    =SUMIF(A3:A7,"D",INDEX(B3:F7,,MONTH(A1)-1))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Registered User
    Join Date
    01-09-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: SUMIF: Need formula to use month to determine sum_range

    Thanks for the help all, turns out an Offset was the way to go rather than a sumif.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMIF: Need formula to use month to determine sum_range

    Good deal. Thanks for the feedback!

    How about marking the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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: 2
    Last Post: 05-01-2013, 11:53 AM
  2. Using results of formula as Sum_Range in a SUMIF
    By sforney123 in forum Excel General
    Replies: 2
    Last Post: 07-14-2011, 11:35 AM
  3. SUMIF where sum_range consists of text strings?
    By arnee84 in forum Excel General
    Replies: 4
    Last Post: 07-14-2011, 07:09 AM
  4. SUMIF [sum_range] question
    By excellrookie in forum Excel General
    Replies: 3
    Last Post: 03-28-2011, 03:13 PM
  5. SUMIF function with mutiple SUM_RANGE values
    By shep85 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-01-2010, 02:18 PM

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