+ Reply to Thread
Results 1 to 6 of 6

Summing Range based on Criteria of Two Other Ranges

  1. #1
    Registered User
    Join Date
    11-14-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Summing Range based on Criteria of Two Other Ranges

    I have a fairly large Sheet with data similar to below where I've labeled the columns A-D.
    What I'm trying to do is Sum the values in Column A for each unique value in Column B AND in Column C, then show those values as new Columns (E) and (F).

    In my example, I added all of the values in Column A that equal "IF" in Column B (AND) had a value of "1" in Column C...and placed that in column F. This is the value I'm trying to obtain for any given range.

    Column E is just the unique value of Column B value appended with Column C value. Column F is the sum of column A.

    HTML Code: 

    Does anyone have an idea of how I can do this?

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

    Re: Summing Range based on Criteria of Two Other Ranges

    For criteria "IF" in col. B and criteria 1 in col. C, use the SUMIFS formula given below....

    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.

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Summing Range based on Criteria of Two Other Ranges

    The values you put there in column E (IF1 IF2 TE2)
    Are those all in one cell?

    Try

    =SUMIFS(A$1:A$100,B$1:B$100,LEFT(E1,2),C$1:C$100,RIGHT(E1,1)+0)

    E1 = IF1

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

    Re: Summing Range based on Criteria of Two Other Ranges

    Or This
    Copy and paste in G2

    =SUMPRODUCT(($B$2:$B$12&$C$2:$C$12=F2)*($A$2:$A$12))

    A
    B
    C
    D
    E
    F
    G
    1
    2
    0.8
    IF
    1
    IF1
    2.4
    3
    1.6
    TE
    2
    IF2
    4.8
    4
    0.4
    TR
    1
    TE2
    3.2
    5
    0.8
    IF
    1
    6
    0.8
    IF
    1
    7
    0.8
    IF
    2
    8
    1.2
    IF
    2
    9
    1.2
    IF
    2
    10
    1.6
    IF
    2
    11
    1.6
    TE
    2
    12
    0.4
    TR
    1
    13
    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

  5. #5
    Registered User
    Join Date
    11-14-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Summing Range based on Criteria of Two Other Ranges

    Alkey, your solution worked perfect!
    I always seem to over complicate things.
    Thank you so much.

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

    Re: Summing Range based on Criteria of Two Other Ranges

    You're welcome and don't forget to thank those who helped by clicking on Add Reputation *

+ 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. How to sum a range of offset ranges based on criteria
    By beaumonr in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-31-2012, 07:58 PM
  2. Replies: 13
    Last Post: 07-08-2009, 04:27 PM
  3. Replies: 5
    Last Post: 08-14-2007, 09:57 AM
  4. Summing or avg moving ranges based on week and day???
    By dexterslabmi@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-28-2006, 04:55 PM
  5. sum the values of a range based upon multiple ranges and criteria
    By LiveIt... in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-17-2006, 04:23 PM

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