+ Reply to Thread
Results 1 to 8 of 8

Nested Sumif's or how to sum data based on nested criteria

  1. #1
    Registered User
    Join Date
    10-07-2014
    Location
    Lansing, Michigan
    MS-Off Ver
    2013
    Posts
    4

    Nested Sumif's or how to sum data based on nested criteria

    Hello,

    I'm new to the forum but this looks like a great resource for excel questions. Here is my puzzle...

    Included type value
    y i 15
    n c 5
    y i 20
    y e 6
    y e 7
    y c 9
    n i 6
    y a 10

    I want a bottom row to sum the 'value' column where the 'included' column is 'y' and then the highest value for each unique letter in 'type'. So in this example the answer would be 20 (for the 'i' value) + 9 (for the 'c' value) + 10 (for the 'a' value) + 7 (for the 'e' value) = 46.

    Any assistance is greatly appreciated.
    Last edited by dlietz; 10-07-2014 at 10:54 AM. Reason: add tags

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Nested Sumif's or how to sum data based on nested criteria

    Maybe this

    =MAX(IF(($A$2:$A$9="y")*($B$2:$B$9=E2),$C$2:$C$9))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    A
    B
    C
    D
    E
    F
    1
    Result
    2
    y i
    15
    a
    10
    3
    n c
    5
    c
    9
    4
    y i
    20
    e
    7
    5
    y e
    6
    i
    20
    6
    y e
    7
    7
    y c
    9
    8
    n i
    6
    9
    y a
    10
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Nested Sumif's or how to sum data based on nested criteria

    The hardest part of this logic is the "max per type" inclusion. Trying to come up with a general solution by creating an intermediate array of unique values that could drive a loop in VBA function....

    Well, first I want to ask, what are the constraints on that "type" column? Is there a maxium of ten possible "types" or 100? What are the constraints for what the string could be; is it always a single lowercase letter or what?
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  4. #4
    Registered User
    Join Date
    10-07-2014
    Location
    Lansing, Michigan
    MS-Off Ver
    2013
    Posts
    4

    Re: Nested Sumif's or how to sum data based on nested criteria

    Alkey,

    I can work with this, I think. I'll have to do some more testing to make sure.

    Thanks much.

  5. #5
    Registered User
    Join Date
    10-07-2014
    Location
    Lansing, Michigan
    MS-Off Ver
    2013
    Posts
    4

    Re: Nested Sumif's or how to sum data based on nested criteria

    Ben,

    The value for type will consist of 10 or less different values, it will always be lower case letters and no more than two characters. The solution that Alkey posted, while it does require me to add separate columns for the totals of each type, seems to work. If you think there may be a way to do it without adding the extra columns I would appreciate your input.

    Thanks.

  6. #6
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Nested Sumif's or how to sum data based on nested criteria

    Honestly if you're fine with an intermediate table, that's probably best: this whole "SUM of maxes per type" operation is unusual, so just putting it at the bottom like a total would be deceptive. Running out a table of MAX values, and then summing up those, would be immediately clear.

  7. #7
    Registered User
    Join Date
    10-07-2014
    Location
    Lansing, Michigan
    MS-Off Ver
    2013
    Posts
    4

    Re: Nested Sumif's or how to sum data based on nested criteria

    I think that makes sense as well, thanks for your input.

  8. #8
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,213

    Re: Nested Sumif's or how to sum data based on nested criteria

    I've got the formula to pull the largest value per letter done if someone wants to take over and try and solve this without a helper column.

    I'm out the door and don't have time to finish it, but I think this gets you about halfway there.

    It pulls the largest value after sorting the list alphabetically, so use a lookup/index match to get the first value...idk. I can't think anymore.

    CSE
    =MAX(IF(($A$2:$A$9="y")*($B$2:$B$9=CHAR(SMALL(CODE($B$2:$B$9),ROWS($B$2:B2)))),$C$2:$C$9))
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. SUMIF with Nested Criteria
    By lost in excel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-19-2012, 10:57 AM
  2. [SOLVED] Nested IF statement to return a value based on multiple criteria
    By harrydnyc in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-27-2011, 11:45 AM
  3. Nested SUMIF statement or multiple SUMIF's
    By Dan27 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-17-2009, 06:55 AM
  4. Nested SUMIF?
    By Ctech in forum Excel General
    Replies: 2
    Last Post: 02-18-2006, 12:37 PM
  5. nested sumif or sumif with two criteria
    By dshigley in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-04-2005, 11:06 PM

Tags for this Thread

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