+ Reply to Thread
Results 1 to 7 of 7

How to selectively sum multiple criteria using only one range?

  1. #1
    Registered User
    Join Date
    12-30-2010
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    How to selectively sum multiple criteria using only one range?

    I have a simple problem and I don't know how to solve this issue. I've tried the sumifs function but it will only allow using multiple criteria from *different* ranges...not from the same range.

    So I have two columns. One with names and the second column with prices. I want to selectively sum the prices from criteria using the names column choosing only certain names and ignoring the rest. How can I do this? Sumif only allows one criteria for one range while sumifs only allows mutliple criteria using different ranges. I need a solution to sum multiple criteria from *one* range only. Is there a solution for this?

    Thanks in advance.
    Last edited by DorothyFan1; 10-23-2011 at 06:36 PM.

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to selectively sum multiple criteria using only one range?

    try sumifs instead
    oh you have i misread it!
    Last edited by martindwilson; 10-23-2011 at 02:34 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: How to selectively sum multiple criteria using only one range?

    You can sum for multiple criteria like this

    =SUMPRODUCT(SUMIF(Names,List,Prices))

    where List contains your list of names that you wish to sum for
    Audere est facere

  4. #4
    Registered User
    Join Date
    12-30-2010
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: How to selectively sum multiple criteria using only one range?

    Quote Originally Posted by daddylonglegs View Post
    You can sum for multiple criteria like this

    =SUMPRODUCT(SUMIF(Names,List,Prices))

    where List contains your list of names that you wish to sum for
    How do I list multiple names in the sumif function using your solution? Doesn't sumif only work on one criteria?

    I was thinking about something like

    =sum(if((a2:a11="Tom")+(a2:a11="Jerry"),D2:D11))

    But when I run that function...instead of only summing the prices for the two names I put in quotes...I get a running total listing all the names and their prices. Why isn't this one working right?

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to selectively sum multiple criteria using only one range?

    try
    =SUM(SUMIF($A$1:$A$10,{"martin","fred"},B$1:B$10))

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: How to selectively sum multiple criteria using only one range?

    Quote Originally Posted by DorothyFan1 View Post
    How do I list multiple names in the sumif function using your solution?
    My suggestion assumes that you have a named range called List with the Names you need.

    martin's suggestion does the same but with the names listed in the formula. If you have martin in D2 and fred in D3 then it can also be

    =SUMPRODUCT(SUMIF($A$1:$A$10,D2:D3,B$1:B$10))

    Quote Originally Posted by DorothyFan1 View Post
    =sum(if((a2:a11="Tom")+(a2:a11="Jerry"),D2:D11))
    This is also a workable solution......but it's an "array formula". Put the formula in a cell, press F2 key to select formula then hold down CTRL and SHIFT keys and press ENTER

    Curly braces should appear around the formula in the formula bar and the formula should give the result you want.....
    Last edited by daddylonglegs; 10-23-2011 at 03:07 PM.

  7. #7
    Registered User
    Join Date
    12-30-2010
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: How to selectively sum multiple criteria using only one range?

    Quote Originally Posted by martindwilson View Post
    try
    =SUM(SUMIF($A$1:$A$10,{"martin","fred"},B$1:B$10))
    OMG! Thanks alot for this one. What a trick to remember!! I didn't know you could put multiple criteria in curly braces using the SUMIF function! Talk about a way to get around the sumif limitation! This is fantastic. Where did you find this trick?! I tried looking this tip up in Bill Jelen's Microsoft Excel 2010 in Depth and it doesn't even mention using curly braces as a way to do this. This tip is worth it's weight in gold! I need to remember this one. Wow.

    This trick helps me get around the SUMIFS function limitation of requiring multiple ranges for different criteria. That little detail limits the SUMIFS power. This trick puts rings around that function as I rarely need to use multiple columns for criteria for summing a single range. This is an incredible find. Thanks a ton for this. Best thing about this trick is I don't have to use the Ctrl Shift Enter key to enter an array formula. I'm still kicking myself in the head why I couldn't find the curly brace trick anywhere else.

    I really hope there's an explanation why Bill Jelen's book doesn't mention this curly braces trick. I'm amazed it's not mentioned at all in the function construction methodology for the SUMIF function. To be fair I don't see this information anywhere else.

    A quick little note...this only works if you use the sum function in conjunction with the sumif function. I tried using it without the sum function and it ignores the second criteria. But when I use the sum function and nest the sumif formula using the curly braces trick, it works. I still can't get over how incredible this trick is. This one is going to save me a ton of grief. I've been trying without luck to using DSUM, and SUBTOTAL with filter in list...and it was just too consuming. This one really cuts down on the experimentation phase in writing a formula to conditionally sum multiple criteria in just ONE range.

    Anyway, thanks for the great tip. I'll list this one as solved.
    Last edited by DorothyFan1; 10-23-2011 at 06:36 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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