+ Reply to Thread
Results 1 to 7 of 7

Changing SUM_Range in a SUMIFS or another formula

  1. #1
    Registered User
    Join Date
    02-11-2019
    Location
    Bangaore
    MS-Off Ver
    2016
    Posts
    16

    Post Changing SUM_Range in a SUMIFS or another formula

    I have a formula in a cell as below

    =SUMIFS(N:N,D:D,AE19,K:K,AD$19)

    In cell A1, i want to have a drop-down list from JAN to DEC (all twelve months). Based on the month i select, i want the row "N:N" to change to a different value.
    If A1 is Jan, then in SUMIFS should have N:N as range, if it is Feb, then the next column (O:O), for MAR it should be P:P...so on for all twelve months.

    How to do this? also, can the solution you provide be used in other formulas as well? there are several other formula that use range N:N & i want all of them to change when the month is changed.

    Thanks for your time in advance.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Changing SUM_Range in a SUMIFS or another formula

    Do the N, O,... columns have a header?

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Changing SUM_Range in a SUMIFS or another formula

    Try this:

    =SUMIFS(INDEX(N:Y,,MONTH(1&A1)),D:D,AE19,K:K,AD$19)

  4. #4
    Registered User
    Join Date
    02-11-2019
    Location
    Bangaore
    MS-Off Ver
    2016
    Posts
    16

    Re: Changing SUM_Range in a SUMIFS or another formula

    Quote Originally Posted by Pepe Le Mokko View Post
    Do the N, O,... columns have a header?
    No. There is no header. Also, the range is not the whole column (only from N24 to N999). Thanks.

  5. #5
    Registered User
    Join Date
    02-11-2019
    Location
    Bangaore
    MS-Off Ver
    2016
    Posts
    16

    Re: Changing SUM_Range in a SUMIFS or another formula

    Quote Originally Posted by Phuocam View Post
    Try this:

    =SUMIFS(INDEX(N:Y,,MONTH(1&A1)),D:D,AE19,K:K,AD$19)
    Hello, As i mentioned below, the range is not the whole column. So, when I put SUMIFS(INDEX($N$24:$N$999:$Y$24:$Y$999,,MONTH(1&A1)),D:D,AE19,K:K,AD$19), it is not working i get "VALUE!" error.
    when i go through the steps pf the formula, it seems correctly take the rage but the output is an error!

  6. #6
    Registered User
    Join Date
    02-11-2019
    Location
    Bangaore
    MS-Off Ver
    2016
    Posts
    16

    Re: Changing SUM_Range in a SUMIFS or another formula

    Quote Originally Posted by mohanys View Post
    Hello, As i mentioned below, the range is not the whole column. So, when I put SUMIFS(INDEX($N$24:$N$999:$Y$24:$Y$999,,MONTH(1&A1)),D:D,AE19,K:K,AD$19), it is not working i get "VALUE!" error.
    when i go through the steps pf the formula, it seems correctly take the rage but the output is an error!
    Sorry, got it! i have to change the D & K columns also to $D$24:$D$999 & $K$24:$K$999 respectively. Thank you for the help.

  7. #7
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Changing SUM_Range in a SUMIFS or another formula

    Change:

    Quote Originally Posted by mohanys View Post
    SUMIFS(INDEX($N$24:$N$999:$Y$24:$Y$999,...
    to:

    =SUMIFS(INDEX($N$24:$Y$999,,MONTH(1&A1)),$D$24:$D$999,AE19,$K$24:$K$999,AD$19)

+ 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. Using SUMIFS (sum_range) to return total difference
    By uk-excell in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-15-2015, 08:38 AM
  2. SUMIF: Need formula to use month to determine sum_range
    By bbrunof in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-29-2014, 11:46 AM
  3. Replies: 2
    Last Post: 08-20-2013, 03:02 PM
  4. [SOLVED] Changing SUMIFS formula from word to word in a cell location
    By Car7os in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-20-2013, 06:43 AM
  5. SUMIFS Function: how to make the "sum_range" argument as a non-fixed variable
    By brunocinelli in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-12-2013, 12:55 PM
  6. [SOLVED] Sumifs formula, how to use named range for Sum_Range
    By Kuehl5000 in forum Excel General
    Replies: 2
    Last Post: 02-26-2013, 11:18 AM
  7. 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

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