Results 1 to 9 of 9

Thread: Sum values based on multiple criteria

  1. #1
    Junior Member
    Join Date
    Jan 2013
    Posts
    19
    Rep Power
    0

    Sum values based on multiple criteria

    Hi

    I would really appriciate if I could get some help with this dilemma.

    In the yellow cell I want a formula that can sum up all the numbers in row D, based on which account numbers I choose in the box in row J. They have to be linked to the account number in row C.
    So since I already have choosen the numbers 4000 and 4050, the amount in the yellow cell should be 47275.

    I also have to be able to change the numbers in the box to get new results as I please. I need to be able to choose account numbers and automatically get new answers in the yellow cell.

    Thank you!

    Dilemma.xlsx
    Last edited by Jorrg1; 01-04-2013 at 02:40 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi Jorrg1,

    Welcome to Excelfox!!

    try

    =SUMPRODUCT(SUMIF(C5:C15,J20:J27,D5:D15))

    or

    =SUM(INDEX(SUMIF(C5:C15,J20:J27,D5:D15),0,0))
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Jan 2013
    Posts
    19
    Rep Power
    0
    ah, thank you very much!!

  4. #4
    Junior Member
    Join Date
    Dec 2012
    Posts
    16
    Rep Power
    0
    Hi

    Or another version:
    =SUMIF(C5:C15,J20,D5:D15)+SUMIF(C5:C15,J21,D5:D15)

    Kevin

  5. #5
    Junior Member
    Join Date
    Jan 2013
    Posts
    19
    Rep Power
    0
    Is it also possible to link the names from the box in row J to M to formula? I have already locked some of them with 4F.

    =SUMMERPRODUKT(SUMMERHVIS($C$5:$C$15;$J$20:$J$27;D 5:D15))

    Please see attached file

    Dilemma 2.xlsx

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    in D19 and copied down & across,

    =SUMPRODUCT(SUMIF($C$5:$C$15,INDEX($J$20:$M$27,,MA TCH($C19,$J$19:$M$19,0)),D$5:D$15))

    Note: Some of the descriptions have trailing spaces. So please remove it to get the correct results.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #7
    Junior Member
    Join Date
    Jan 2013
    Posts
    19
    Rep Power
    0
    Thank you very much. I tried to translate it from english to norwegian but I didnt get the right result. Is there something wrong with my formula? I pasted the formula in D19..

    =SUMMERPRODUKT(SUMMERHVIS($C$5:$C$15;INDEKS($J$20: $M$27;SAMMENLIGNE($C19;$J$19:$M$19;0));D$5:D$15))

    Transelation, from english to norwegian:

    Sumproduct = summerprodukt, sumif = summerhvis, index = indeks, match = sammenligne,

    We also use ";" instead of ","

    Dilemma 3.xlsx

  8. #8
    Junior Member
    Join Date
    Jan 2013
    Posts
    19
    Rep Power
    0
    I managed My formula had some trailing spaces

    thank you!

  9. #9
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Thanks for the feedback. I had already mentioned the trailing space in my previous post
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  2. SUM Value With Multiple Criteria In Same Column
    By MarkJohn in forum Excel Help
    Replies: 2
    Last Post: 05-18-2012, 07:52 AM
  3. Sum values based on multiple criteria
    By vmath in forum Excel Help
    Replies: 1
    Last Post: 05-07-2012, 08:53 AM
  4. Fetch multiple values based on criteria
    By Lucero in forum Excel Help
    Replies: 8
    Last Post: 04-07-2012, 12:35 PM
  5. SUM with Multiple Criteria
    By candygirl in forum Excel Help
    Replies: 5
    Last Post: 12-11-2011, 03:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •