sumif with multiple critera range and sum_range

Discussion in 'Microsoft Excel Worksheet Functions' started by DocBrown, Dec 20, 2008.

  1. DocBrown

    DocBrown Guest

    Hello,

    I have a worksheet where I'm trying to use the sumif function to sum over
    multiple criteria ranges and multiple sum_ranges. The following formula,
    which shows three ranges, (surprisingly) works. Is there a more compact way
    to implement this? This is for a budget spreadsheet where each of the ranges
    (J31:J36 for example, are one month. So the full scale formula would have 12
    ranges and sum_ranges. Notice that the sum_ranges are offset two columns
    right of the critera range.

    =SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36))

    If I rearranged the columns, would it that make it possible for a more
    compact formula to work?

    Thanks,
    John
     
    DocBrown, Dec 20, 2008
    #1
    1. Advertisements

  2. For details on SUMPRODUCT
    Bob Phillips
    http://www.xldynamic.com/source/xld.SUMPRODUCT.html
    J.E McGimpsey
    http://mcgimpsey.com/excel/formulae/doubleneg.html

    --
    Bernard V Liengme
    Microsoft Excel MVP
    http://people.stfx.ca/bliengme
    remove caps from email

    "DocBrown" <> wrote in message
    news:...
    > Hello,
    >
    > I have a worksheet where I'm trying to use the sumif function to sum over
    > multiple criteria ranges and multiple sum_ranges. The following formula,
    > which shows three ranges, (surprisingly) works. Is there a more compact
    > way
    > to implement this? This is for a budget spreadsheet where each of the
    > ranges
    > (J31:J36 for example, are one month. So the full scale formula would have
    > 12
    > ranges and sum_ranges. Notice that the sum_ranges are offset two columns
    > right of the critera range.
    >
    > =SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36))
    >
    > If I rearranged the columns, would it that make it possible for a more
    > compact formula to work?
    >
    > Thanks,
    > John
    >
     
    Bernard Liengme, Dec 20, 2008
    #2
    1. Advertisements

  3. DocBrown

    T. Valko Guest

    >=SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36))

    Although Excel accepts the formula like that each "middle" range is being
    ignored. That formula can be written like this and will produce the same
    result:

    =SUMIF($J$31:$T$36,C47,$L$31:$V$36)

    Here is the pattern it's following:

    Criteria range.....Sum range
    ............J....................L
    ............K..................M
    ............N..................P
    ............O..................Q
    ............R...................T
    ............S...................U
    ............V.....................

    If you want to restructure your setup so you can squeeze in 12 months worth
    of data in the least amount of space then use every other column.

    C = criteria range
    S = sum range

    C...S...C...S...C...S...C...S etc
    J....K...L...M..N..O...P...Q etc

    Then the offset in the formula is 1:

    =SUMIF($J$31:$P$36,C47,$K$31:$Q$36)


    --
    Biff
    Microsoft Excel MVP


    "DocBrown" <> wrote in message
    news:...
    > Hello,
    >
    > I have a worksheet where I'm trying to use the sumif function to sum over
    > multiple criteria ranges and multiple sum_ranges. The following formula,
    > which shows three ranges, (surprisingly) works. Is there a more compact
    > way
    > to implement this? This is for a budget spreadsheet where each of the
    > ranges
    > (J31:J36 for example, are one month. So the full scale formula would have
    > 12
    > ranges and sum_ranges. Notice that the sum_ranges are offset two columns
    > right of the critera range.
    >
    > =SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36))
    >
    > If I rearranged the columns, would it that make it possible for a more
    > compact formula to work?
    >
    > Thanks,
    > John
    >
     
    T. Valko, Dec 20, 2008
    #3
  4. DocBrown

    DocBrown Guest

    Hi Bernard,

    This looks promising, but I think I need a bit more hand holding. Example 4
    looks similar, but I don't see the whole picture. I'll expand on my concept
    and see if that changes the answer. The cell layout is:

    J K L O P Q T U V
    31 one text 5 two text 7 four text 9
    32 two text 6 three text 8 two text 10
    33
    34

    C74 is the value I'm matching if it = "two"
    then sumif returns the sum of L32, Q31, V32.

    Will the SUMPRODUCT be able to do this?

    Thanks,
    John

    "Bernard Liengme" wrote:

    > For details on SUMPRODUCT
    > Bob Phillips
    > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
    > J.E McGimpsey
    > http://mcgimpsey.com/excel/formulae/doubleneg.html
    >
    > --
    > Bernard V Liengme
    > Microsoft Excel MVP
    > http://people.stfx.ca/bliengme
    > remove caps from email
    >
    > "DocBrown" <> wrote in message
    > news:...
    > > Hello,
    > >
    > > I have a worksheet where I'm trying to use the sumif function to sum over
    > > multiple criteria ranges and multiple sum_ranges. The following formula,
    > > which shows three ranges, (surprisingly) works. Is there a more compact
    > > way
    > > to implement this? This is for a budget spreadsheet where each of the
    > > ranges
    > > (J31:J36 for example, are one month. So the full scale formula would have
    > > 12
    > > ranges and sum_ranges. Notice that the sum_ranges are offset two columns
    > > right of the critera range.
    > >
    > > =SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36))
    > >
    > > If I rearranged the columns, would it that make it possible for a more
    > > compact formula to work?
    > >
    > > Thanks,
    > > John
    > >

    >
    >
    >
     
    DocBrown, Dec 21, 2008
    #4
  5. DocBrown

    DocBrown Guest

    That is awsome! It works like a charm. I didn't think the ranges would work
    like that

    Thank you a bunch.

    John

    "T. Valko" wrote:

    > >=SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36))

    >
    > Although Excel accepts the formula like that each "middle" range is being
    > ignored. That formula can be written like this and will produce the same
    > result:
    >
    > =SUMIF($J$31:$T$36,C47,$L$31:$V$36)
    >
    > Here is the pattern it's following:
    >
    > Criteria range.....Sum range
    > ............J....................L
    > ............K..................M
    > ............N..................P
    > ............O..................Q
    > ............R...................T
    > ............S...................U
    > ............V.....................
    >
    > If you want to restructure your setup so you can squeeze in 12 months worth
    > of data in the least amount of space then use every other column.
    >
    > C = criteria range
    > S = sum range
    >
    > C...S...C...S...C...S...C...S etc
    > J....K...L...M..N..O...P...Q etc
    >
    > Then the offset in the formula is 1:
    >
    > =SUMIF($J$31:$P$36,C47,$K$31:$Q$36)
    >
    >
    > --
    > Biff
    > Microsoft Excel MVP
    >
    >
    > "DocBrown" <> wrote in message
    > news:...
    > > Hello,
    > >
    > > I have a worksheet where I'm trying to use the sumif function to sum over
    > > multiple criteria ranges and multiple sum_ranges. The following formula,
    > > which shows three ranges, (surprisingly) works. Is there a more compact
    > > way
    > > to implement this? This is for a budget spreadsheet where each of the
    > > ranges
    > > (J31:J36 for example, are one month. So the full scale formula would have
    > > 12
    > > ranges and sum_ranges. Notice that the sum_ranges are offset two columns
    > > right of the critera range.
    > >
    > > =SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36))
    > >
    > > If I rearranged the columns, would it that make it possible for a more
    > > compact formula to work?
    > >
    > > Thanks,
    > > John
    > >

    >
    >
    >
     
    DocBrown, Dec 21, 2008
    #5
  6. DocBrown

    T. Valko Guest

    You're welcome. Thanks for the feedback!

    --
    Biff
    Microsoft Excel MVP


    "DocBrown" <> wrote in message
    news:...
    > That is awsome! It works like a charm. I didn't think the ranges would
    > work
    > like that
    >
    > Thank you a bunch.
    >
    > John
    >
    > "T. Valko" wrote:
    >
    >> >=SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36))

    >>
    >> Although Excel accepts the formula like that each "middle" range is being
    >> ignored. That formula can be written like this and will produce the same
    >> result:
    >>
    >> =SUMIF($J$31:$T$36,C47,$L$31:$V$36)
    >>
    >> Here is the pattern it's following:
    >>
    >> Criteria range.....Sum range
    >> ............J....................L
    >> ............K..................M
    >> ............N..................P
    >> ............O..................Q
    >> ............R...................T
    >> ............S...................U
    >> ............V.....................
    >>
    >> If you want to restructure your setup so you can squeeze in 12 months
    >> worth
    >> of data in the least amount of space then use every other column.
    >>
    >> C = criteria range
    >> S = sum range
    >>
    >> C...S...C...S...C...S...C...S etc
    >> J....K...L...M..N..O...P...Q etc
    >>
    >> Then the offset in the formula is 1:
    >>
    >> =SUMIF($J$31:$P$36,C47,$K$31:$Q$36)
    >>
    >>
    >> --
    >> Biff
    >> Microsoft Excel MVP
    >>
    >>
    >> "DocBrown" <> wrote in message
    >> news:...
    >> > Hello,
    >> >
    >> > I have a worksheet where I'm trying to use the sumif function to sum
    >> > over
    >> > multiple criteria ranges and multiple sum_ranges. The following
    >> > formula,
    >> > which shows three ranges, (surprisingly) works. Is there a more compact
    >> > way
    >> > to implement this? This is for a budget spreadsheet where each of the
    >> > ranges
    >> > (J31:J36 for example, are one month. So the full scale formula would
    >> > have
    >> > 12
    >> > ranges and sum_ranges. Notice that the sum_ranges are offset two
    >> > columns
    >> > right of the critera range.
    >> >
    >> > =SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36))
    >> >
    >> > If I rearranged the columns, would it that make it possible for a more
    >> > compact formula to work?
    >> >
    >> > Thanks,
    >> > John
    >> >

    >>
    >>
    >>
     
    T. Valko, Dec 21, 2008
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    SumIf where sum_range contains text

    Guest, Nov 13, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    5
    Views:
    579
    Peo Sjoblom
    Nov 14, 2003
  2. Guest

    SUMIF with multi-column sum_range

    Guest, Nov 17, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    228
    Guest
    Nov 17, 2004
  3. Oscar
    Replies:
    2
    Views:
    791
    Bob Phillips
    Jan 11, 2005
  4. dza7

    Sumif with multiple columns in sum_range

    dza7, Mar 14, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    17
    Views:
    1,675
    Ron Coderre
    Mar 28, 2008
  5. caro

    sumif and complex sum_range formulae

    caro, Feb 3, 2009, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    190
Loading...

Share This Page