+ Reply to Thread
Results 1 to 10 of 10

How to sum multiple rows horizontally with the matched criteria

  1. #1
    Registered User
    Join Date
    06-15-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    How to sum multiple rows horizontally with the matched criteria

    Question: How to formulate a function to add value across horizontally if In Service Date matches with the date listed Date?

    Capture.JPGCapture.JPG

    For example: Cell B9 should equal to sum of B2:F2 and B4:F4.

    I tried to use sum(index(array, match()) function but it only works with single match value. For example, 02/01/2011 have two rows, the values on the 2nd row won't add up to the total. Could anyone help to get this resolve?

    Many thanks!!

  2. #2
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: How to sum multiple rows horizontally with the matched criteria

    Hi Cute Petals

    Try This..
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Now drag downward
    Regards,
    Deb
    Last edited by Debraj Roy; 06-15-2013 at 03:57 AM.

  3. #3
    Registered User
    Join Date
    06-15-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to sum multiple rows horizontally with the matched criteria

    it returns with the #Value! error

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

    Re: How to sum multiple rows horizontally with the matched criteria

    There's absolutely nothing wrong with that formula. Post a sample showing the error rather than JPEGs.
    Click * below if this answer helped

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

  5. #5
    Registered User
    Join Date
    06-15-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to sum multiple rows horizontally with the matched criteria

    how to post a sample? sorry, I'm new here

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

    Re: How to sum multiple rows horizontally with the matched criteria

    Cick on 'Go Advanced' and scroll down til you see 'Manage Attachments'/Add Files/Select Files.

    Regards

  7. #7
    Registered User
    Join Date
    06-15-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to sum multiple rows horizontally with the matched criteria

    I'm sorry, it works when I copy and paste the exactly formula it. I might have type it wrong the first time.

    Could you explain the formula a little bit? Is it if the ($A$2:$A$6=$A9), it will equal to 1 and multiple across the same row automatically?

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

    Re: How to sum multiple rows horizontally with the matched criteria

    Pretty much. Try using the Evaluate Formula feature in Excel and go through it step by step and you might be able to work out what's going on.

    Basically, it checks to see which of $A$2:$A$6 is equal to $A9 and, for the case(s) where that is true, it sums up all the values in the corresponding row(s).

    Regards

  9. #9
    Registered User
    Join Date
    06-15-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to sum multiple rows horizontally with the matched criteria

    thanks a lot

  10. #10
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: How to sum multiple rows horizontally with the matched criteria

    @ XOR LX..

    Thanks for the support..
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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