How to sum multiple rows horizontally with the matched criteria

**Cute_Petals** · 06-15-2013, 03:38 AM

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

Capture.JPG Capture.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!!

**Debraj Roy** · 06-15-2013, 03:50 AM

Hi Cute Petals

Try This..

Formula:

Please Login or Register  to view this content.

Now drag downward
Regards,
Deb

**Cute_Petals** · 06-15-2013, 11:43 AM

it returns with the #Value! error

**XOR LX** · 06-15-2013, 12:22 PM

There's absolutely nothing wrong with that formula. Post a sample showing the error rather than JPEGs.

**Cute_Petals** · 06-15-2013, 12:25 PM

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

**XOR LX** · 06-15-2013, 12:29 PM

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

Regards

**Cute_Petals** · 06-15-2013, 12:39 PM

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?

**XOR LX** · 06-15-2013, 12:46 PM

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

**Cute_Petals** · 06-15-2013, 12:47 PM

thanks a lot

**Debraj Roy** · 06-15-2013, 01:21 PM

@ XOR LX..

Thanks for the support..

How to sum multiple rows horizontally with the matched criteria

LinkBack

Thread Tools

Rate This Thread

Display

How to sum multiple rows horizontally with the matched criteria

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions