Results 1 to 6 of 6

Thread: SumIF on varying columns for months
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2004
    Posts
    195
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SumIF on varying columns for months

    I have a spreadsheet with differing prices in Column K. I have 12 Columns (AQ through BB) representing each of the months. A "1" in the appropriate month column tells me that that row relates to the month of the Column -- thus AR is for the month of February, etc.

    I want to sum those prices in Column K for those rows which pertain to the current month.

    This formula works: =SUMIF(AR10:AR838,"=1",K10:K838) and gives me what I want.

    However, I need to remember each month to change the formula, so that, when March arrives I'll need to change the forumla to =SUMIF(AS10:AS838,"=1",K10:K838) -- since AS is the column for March.

    I'd like to have a forumla that would adjust automatically according to the current month.

    In cell T1 I have =Today() and I can do a =Month(T1) which gives me a 2 as the answer, which, of course, relates to the second month, thus february.

    I'm struggling to find a way to use the current month to trigger which Column should be used in the =SUMIF(AR10:AR838,"=1",K10:K838) formula.

    In words what I want is a way to say, "use the current month to select the appropriate month column for the SUMIF function.

    Thanks for any help

  2. #2
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,256
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SumIF on varying columns for months

    Quote Originally Posted by StanSz View Post
    I have a spreadsheet with differing prices in Column K. I have 12 Columns (AQ through BB) representing each of the months. A "1" in the appropriate month column tells me that that row relates to the month of the Column -- thus AR is for the month of February, etc.

    I want to sum those prices in Column K for those rows which pertain to the current month.

    This formula works: =SUMIF(AR10:AR838,"=1",K10:K838) and gives me what I want.

    However, I need to remember each month to change the formula, so that, when March arrives I'll need to change the forumla to =SUMIF(AS10:AS838,"=1",K10:K838) -- since AS is the column for March.

    I'd like to have a forumla that would adjust automatically according to the current month.

    In cell T1 I have =Today() and I can do a =Month(T1) which gives me a 2 as the answer, which, of course, relates to the second month, thus february.

    I'm struggling to find a way to use the current month to trigger which Column should be used in the =SUMIF(AR10:AR838,"=1",K10:K838) formula.

    In words what I want is a way to say, "use the current month to select the appropriate month column for the SUMIF function.

    Thanks for any help
    Sheet1

    * A B C K L M T AQ AR AS AT AU AV AW AX AY AZ BA BB
    1 * * * * * * 01-Mar * * * * * * * * * * * *
    2 * * * * * * * * * * * * * * * * * * *
    3 * * * * * * * * * * * * * * * * * * *
    4 * * * * * * * * * * * * * * * * * * *
    5 * * * * * * * * * * * * * * * * * * *
    6 * * * * * * * * * * * * * * * * * * *
    7 * * * * * * * * * * * * * * * * * * *
    8 * * * * * * * * * * * * * * * * * * *
    9 * * * * * * * January February March April May June July August September October November December
    10 * 56 * 30 * * * * 1 1 * * * * * * * * *
    11 * * * 22 * * * * * 2 * * * * * * * * *
    12 * * * 26 * * * * * 1 * * * * * * * * *
    13 * * * * * * * * * * * * * * * * * * *
    14 * * * * * * * * * * * * * * * * * * *
    15 * * * * * * * * * * * * * * * * * * *

    Spreadsheet Formulas
    Cell Formula
    B10 {=SUMIF(INDEX(AQ10:BB838,0,MATCH(MONTH(T1),MONTH($AQ$9:$BB$9),0)),"=1",K10:K838)}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

    Not sure where you headers are but if in fifferent row just change it.
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  3. #3
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SumIF on varying columns for months

    Hi,

    Maybe:

    Code:
    =SUMIF(
       INDEX(AQ10:BB838, 0, MONTH(T1)),
       1,
       K10:K838)

  4. #4
    Board Regular
    Join Date
    Sep 2004
    Posts
    195
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SumIF on varying columns for months

    Quote Originally Posted by circledchicken View Post
    Hi,

    Maybe:

    Code:
    =SUMIF(
       INDEX(AQ10:BB838, 0, MONTH(T1)),
       1,
       K10:K838)
    Thanks -- this works wonderfully -- Now I'll have to analyze the forumla so that I can understand it.

    Best wishes,

    I will also check out the Array formula.

  5. #5
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SumIF on varying columns for months

    Quote Originally Posted by StanSz View Post
    Thanks -- this works wonderfully -- Now I'll have to analyze the forumla so that I can understand it.

    Best wishes,

    I will also check out the Array formula.
    You're welcome, thanks for the feedback.

    Its just a normal SUMIF but the first argument picks out the appropriate column.

    Where the row_num parameter is set to zero in the INDEX function, it returns the entire column specified by the column_num parameter (i.e. the month in your case).

    A couple of excellent articles for a better understanding of the INDEX and SUMIF functions are:

  6. #6
    Board Regular
    Join Date
    Sep 2004
    Posts
    195
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SumIF on varying columns for months

    Quote Originally Posted by circledchicken View Post
    You're welcome, thanks for the feedback.

    Its just a normal SUMIF but the first argument picks out the appropriate column.

    Where the row_num parameter is set to zero in the INDEX function, it returns the entire column specified by the column_num parameter (i.e. the month in your case).

    A couple of excellent articles for a better understanding of the INDEX and SUMIF functions are:

    Thanks again

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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