+ Reply to Thread
Results 1 to 4 of 4

Sum of Cells Based on Multiple Criteria

  1. #1
    Registered User
    Join Date
    08-25-2008
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    80

    Sum of Cells Based on Multiple Criteria

    Basically, what I want to do is search for a specific entity code that may be in multiple rows, and if any of those rows contain one of three specific account codes, I want to take the sum. For example:

    Entity Code Account Code Amount
    1 20 100
    1 25 220
    1 40 160
    1 50 70
    1 60 95
    1 70 110
    2 30 100
    2 25 220
    2 55 160
    2 50 70
    2 65 95
    2 70 110

    This is just a quick example I put together since I'm not the best at copying and pasting pictures of my actual spreadsheet. What I want to do is search for the entity, and if any of the account codes match 20, 50 or 60, I want it to take the number from the amount column and sum them. So for entity 1, the total would be 265 (100+70+95) since it contains all of the accounts. Entity 2 would be 70 since it does not contain account code 20 or 60, just 50.

    I hope this makes sense. I thought a SUMIF would do the trick, but I can't seem to get that to work. Please let me know if you have any questions or require any additional information.

    Thanks!

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sum of Cells Based on Multiple Criteria

    Hi,

    Assuming the table you give is in A1:C13 (with headers in row 1):

    =SUMPRODUCT(SUMIFS(C2:C13,A2:A13,1,B2:B13,{20,50,60}))

    Even better if you can have the Entity Code and desired Account Code(s) in separate cells, so as to make the formula dynamic, e.g. Entity Code in E1, desired Account Codes in F1:F3:

    =SUMPRODUCT(SUMIFS(C2:C13,A2:A13,E1,B2:B13,F1:F3))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    08-25-2008
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    80

    Re: Sum of Cells Based on Multiple Criteria

    Thank you for the quick response! That seems to be working perfectly, thank you!!!

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sum of Cells Based on Multiple Criteria

    You're welcome!

+ 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. Highlighting cells based off multiple criteria
    By mstieler in forum Excel General
    Replies: 0
    Last Post: 02-20-2013, 07:26 PM
  2. Populating Cells Based On Multiple Criteria
    By LordAcoustic in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-01-2013, 12:05 PM
  3. [SOLVED] Calculate cells based on multiple criteria
    By jealkon in forum Excel General
    Replies: 7
    Last Post: 07-11-2012, 02:26 PM
  4. Counting cells based on multiple OR criteria
    By cspafford in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-14-2009, 02:54 PM
  5. Counting cells based on multiple criteria
    By Kayote in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-10-2007, 10:39 AM

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