Results 1 to 5 of 5

SUMIF with variable Sum_Range

This is a discussion on SUMIF with variable Sum_Range within the Excel Questions forums, part of the Question Forums category; I am working on a spreadsheet that calculates the monthly price based on the number of active support criteria. Normally ...

  1. #1
    New Member
    Join Date
    Sep 2009
    Posts
    4

    Default SUMIF with variable Sum_Range

    I am working on a spreadsheet that calculates the monthly price based on the number of active support criteria. Normally I would use a standard SUMIF function, however the column it uses for the sum range needs to be variable based on a criteria (month).

    A B C D
    Jan-09 Feb-09
    Server1 Basic 1 0
    Server2 High 1 1
    Server3 Basic 0 1

    So normally I would use SUMIF(B:B,"Basic",C:C), but now I need it to have the C:C variable based on on the month.

  2. #2
    Board Regular NBVC's Avatar
    Join Date
    Aug 2005
    Location
    Ontario
    Posts
    5,827

    Default Re: SUMIF with variable Sum_Range

    Something like this:

    =SUMPRODUCT((B2:B4="Basic")*(TEXT(C1:D1,"mmm")="Jan"),C2:D4)

    where dates are in C1:D1

    Adjust ranges to suit (don't use whole column references, use defined ranges)

    Also, you can replace "Jan" with a cell reference containing "Jan" without quotes, so that you can be more flexible.
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Microsoft MVP - Excel



  3. #3
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,792

    Default Re: SUMIF with variable Sum_Range

    You could use SUMIF with an INDEX function for the range to sum, e.g.

    =SUMIF(B2:B100,"Basic",INDEX(C2:N100,0,MATCH(P1,C1:N1)))

    where P1 contains a date in the month you want to sum, C1:N1 contains the 1st of each month

  4. #4
    Board Regular
    Join Date
    Aug 2008
    Location
    Copenhagen, Denmark
    Posts
    309

    Default Re: SUMIF with variable Sum_Range

    if column C is jan-09 and column D is feb-09; then it is still
    SUMIF(B:B,"Basic",C:C)
    Regards Sten
    Excel 2007

  5. #5
    New Member
    Join Date
    Sep 2009
    Posts
    4

    Default Re: SUMIF with variable Sum_Range

    NBVC, thanks very much, I knew it should be possible with INDEX MATCH, but was not able to get it right. Works as a dream.

    Barry, I have tried the Sumproduct one as well, but could not get it to work (probably me).

    Thanks for you help,

    Albert

Tags for this Thread

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
  •  


DMCA.com