Results 1 to 5 of 5

Thread: SUMIF with variable Sum_Range

  1. #1

    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

    Default Re: SUMIF with variable Sum_Range

    Something like this:


    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 (2010-2016)

  3. #3
    MrExcel MVP
    barry houdini's Avatar
    Join Date
    Mar 2005

    Default Re: SUMIF with variable Sum_Range

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


    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
    Copenhagen, Denmark

    Default Re: SUMIF with variable Sum_Range

    if column C is jan-09 and column D is feb-09; then it is still
    Regards Sten
    Excel 2007

  5. #5

    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,


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