+ Reply to Thread
Results 1 to 25 of 25

Sum based on multiple selection criteria

  1. #1
    Registered User
    Join Date
    03-26-2015
    Location
    Allentown, PA
    MS-Off Ver
    7
    Posts
    7

    Sum based on multiple selection criteria

    I would like to sum based on multiple selection criteria that are look-ups to another tab.

    For example, I want to calculate the sum of column c (in sheet1) where column A matches a value in column A in sheet2, and where column B = "yes". The result in the below example would be 10.

    sheet1:
    x yes 1
    x yes 2
    x no 3
    x no 4
    y yes 5
    y no 6
    z yes 7
    z no 8

    sheet2:
    x
    z

    Thanks in advance for any help that you can provide!

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum based on multiple selection criteria

    One way...

    Data Range
    A
    B
    C
    D
    E
    F
    1
    x
    yes
    1
    x
    10
    2
    x
    yes
    2
    z
    3
    x
    no
    3
    Yes
    4
    x
    no
    4
    5
    y
    yes
    5
    6
    y
    no
    6
    7
    z
    yes
    7
    8
    z
    no
    8
    9
    ------
    ------
    ------
    ------
    ------
    ------


    This formula entered in F1:

    =SUMPRODUCT(SUMIFS(C1:C8,A1:A8,E1:E2,B1:B8,E3))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Sum based on multiple selection criteria

    Hi mcnallyb and welcome to the forum,

    See if the attached does what you want. You should also update your profile to show what version of Excel you are using as the early versions didn't have the SumIfS () functions.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Sum based on multiple selection criteria

    =sumifs(Sheet1!C1:C:100,Sheet1!A1:A100,Sheet2!A1,Sheet1!B1:B100,"yes)

  5. #5
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Sum based on multiple selection criteria

    Or this.....

    Assuming that your sample data on Sheet1 is in the range A1:C8 and on Sheet2 is in the range A1:A2, then

    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Sum based on multiple selection criteria

    Hi,

    Marvin, Tony and Soberguy all got different answers. All used different formulas. I guess this shows we aren't all experts. You should never trust an answer on this site unless you test it out for yourself. See all three formulas and answers in the attached.

    sktneer just gave an answer that is different still, but at least it looks to be the correct number. (If you agree with MarvinP's answer)
    Attached Files Attached Files
    Last edited by MarvinP; 03-26-2015 at 02:29 PM.

  7. #7
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Sum based on multiple selection criteria

    Marvin, I get invalid link when I click that.
    Please remember to hit the Add Reputation for any member that has been helpful.

  8. #8
    Registered User
    Join Date
    03-26-2015
    Location
    Allentown, PA
    MS-Off Ver
    7
    Posts
    7

    Re: Sum based on multiple selection criteria

    Thanks, but the data will be in different sheets. Just referencing another sheet using "sheet2!" doesn't seem to work.

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Sum based on multiple selection criteria

    Try again as I was updating my workbook to include sktneer's answer too.

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum based on multiple selection criteria

    My formula is the only one that returns the correct result!

    The correct result is 10. These are the rows that meet the criteria: 1, 2 and 7.


  11. #11
    Registered User
    Join Date
    03-26-2015
    Location
    Allentown, PA
    MS-Off Ver
    7
    Posts
    7

    Re: Sum based on multiple selection criteria

    Thanks, Tony. But what is the syntax across sheets? I can't get it to work.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum based on multiple selection criteria

    In my exhibit I put everything on the same sheet to make it easier to see what's happening.

  13. #13
    Registered User
    Join Date
    03-26-2015
    Location
    Allentown, PA
    MS-Off Ver
    7
    Posts
    7

    Re: Sum based on multiple selection criteria

    Thanks, Tony, I did get the syntax correct. I appreciate your help!

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum based on multiple selection criteria

    Quote Originally Posted by mcnallyb2 View Post
    Thanks, Tony. But what is the syntax across sheets? I can't get it to work.
    You didn't mention that the formula needs to work across multiple sheets.

    That changes things significantly!

    What are the real sheet names?

  15. #15
    Registered User
    Join Date
    03-26-2015
    Location
    Allentown, PA
    MS-Off Ver
    7
    Posts
    7

    Re: Sum based on multiple selection criteria

    I thought by mentioning sheet1 and sheet2 in the example, it was clear. Sorry. Thanks again...I go it to work!

  16. #16
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Sum based on multiple selection criteria

    Actually all the three formulas are returning the same result. Marvin changed the values on sheet2 to x,y instead of x,z as per the sample in post#1.
    Attached Files Attached Files

  17. #17
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Sum based on multiple selection criteria

    Tony - my apologies, you did get the answer correct.

    I thought the criteria on sheet2 was x, y and after rereading it, it was x, z (not y). I got the problem wrong, but the right answer for the wrong problem. Does that give me half credit?

    Good thing I don't have this work as my day job...

  18. #18
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Sum based on multiple selection criteria

    Apparently I need to work on interpreting what people want a bit better. I pictured it as either x or z, not both. It's still beyond me how everyone else arrived at that conclusion, even after re-reading the OP.

  19. #19
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Sum based on multiple selection criteria

    Hey sktneer,

    Thanks for fixing my sheet2 "typo" and making the y a z. Maybe I'll get a bigger monitor or new glasses before trying to answer more questions.

  20. #20
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum based on multiple selection criteria

    OK, I think I misunderstood what you meant by "across sheets".

    Sounds like you're good to go!

  21. #21
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Sum based on multiple selection criteria

    Quote Originally Posted by MarvinP View Post
    Hey sktneer,

    Thanks for fixing my sheet2 "typo" and making the y a z. Maybe I'll get a bigger monitor or new glasses before trying to answer more questions.
    Actually I was trying to fix my formula when Tony said that only his formula is returning the correct output then only I found that typo. We all got confused just because of that typo. lol

  22. #22
    Registered User
    Join Date
    03-26-2015
    Location
    Allentown, PA
    MS-Off Ver
    7
    Posts
    7

    Re: Sum based on multiple selection criteria

    Hi Tony,

    Thanks again for your help. I'm trying to expand on the concept to have multiple criteria, each with a range of values. I just added "no" in E4, and want to add it to the selection criteria (sorry, I don't know how to add a table in the reply):

    A B C D E F
    1 x yes 1 x 10
    2 x yes 2 z
    3 x no 3 Yes
    4 x no 4 No
    5 y yes 5
    6 y no 6
    7 z yes 7
    8 z no 8


    Trying to expand the formula, I made it: =SUMPRODUCT(SUMIFS(C1:C8,A1:A8,E1:E2,B1:B8,E3:E4))
    The value returns 11, but I would expect 25.

    Can you please help?
    Attached Images Attached Images

  23. #23
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum based on multiple selection criteria

    Like this...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    1
    x
    yes
    1
    x
    25
    2
    x
    yes
    2
    z
    3
    x
    no
    3
    Yes
    No
    4
    x
    no
    4
    5
    y
    yes
    5
    6
    y
    no
    6
    7
    z
    yes
    7
    8
    z
    no
    8
    9
    ------
    ------
    ------
    ------
    ------
    ------
    ------


    This formula entered in G1:

    =SUMPRODUCT(SUMIFS(C1:C8,A1:A8,E1:E2,B1:B8,E3:F3))

    Note how the criteria ranges are in opposite directions. This is a quirk of the SUMIFS function when using multiple arrays as criteria.

  24. #24
    Registered User
    Join Date
    03-26-2015
    Location
    Allentown, PA
    MS-Off Ver
    7
    Posts
    7

    Re: Sum based on multiple selection criteria

    Thank you! Again, I really appreciate your help!

  25. #25
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum based on multiple selection criteria

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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. [SOLVED] using SUMIFS and adding values based on multiple criteria based on selection from the drop
    By muheebrahman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2015, 12:55 AM
  2. Selection based on multiple criteria
    By jacob@thepenpoint in forum Excel General
    Replies: 1
    Last Post: 11-10-2013, 02:57 PM
  3. [SOLVED] Selection of udf based on criteria
    By SAGAR KHOLLAM in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-29-2013, 04:27 AM
  4. Select multiple criteria based on check box selection and return all unique values.
    By TommyToe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-21-2013, 09:14 AM
  5. Multiple selection criteria, which formula? .. and selection based on unique numbers
    By FalkirkJim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-11-2012, 05:22 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