Formula to match 2 criteria and sum values in row

[rakeshgarg1977]

Hello,
I am attaching a excel sheet with my query included in the same, Request ur help please.

Please note that it is partly same as the one available in

http://www.excelforum.com/showthread...t=#post4522904

But the additional requirement is mentioned in the excel. Please help.

Thanks

[protonLeah]

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[rakeshgarg1977]

Hi Ben, Thanks for providing clarity.I am a fan of all the techies responding and helping people around the world. A big Thanks to all
I understand, but my query was typical that i could not put it in words in the thread without referring Excel sheet.I am putting the same query in thread also

Attached herewith is excel for details about my query and the query is -

I want to find maximum value of AG for say Equipment-1(Column-J), when the column in the cells are filled, if more than one such value observed, i want maximum value of AD(Column-D) among the two or more. Once it is determined, I want to add the Row-3, colum J to O to be added for the equipments common between the Maximum AG (Column-G) & AD (Column-D) and other products.

Example- For equipment-1 (Column-J) is used in Product H,J,L and N.. Maximum value of AG for these 4 products is 61 which is appearing for Product H & J, on evaluating Value of AD ( COlumn-D), maximum value among H & J is for J.

Now Product J (Row13) has two equipments mentioned RMG50 & FBD50, formula shall search these two common equipments for all remaining 3 products i.e. H,L & N and whichever equipment is common between them shall add value of Row-3, column J to 0. Formula shall be in Column P.Same way it shall be for all equipments like 200L, 1200L and so on.Common equipment search for each shall be from Column J to 0.

[JohnTopley]

Please add some sample expected results.

[rakeshgarg1977]

As mentioned in the example- For equipment-1 (Column-J) is used in Product H,J,L and N (Column-A). Maximum value in Column-G among these 4 products is 61 which is appearing for Product H & J, on evaluating Value of AD ( Column-D), maximum value among H & J is for J i.e. 3 against 2 for product H

Now for Product J if we see the Row13, has two equipments cell filled i.e. RMG50 & FBD50. So formula shall search equipment used in remaining 3 products i.e. H,L & N (Row-11,15 and 17 respectively) for common equipment between Product-J and H, Product-J and L and Product-J and N and sum the value of Row-3 for common (SHaring) equipments. Like between Product-J & H the result shall be 200 (shall appear in cell P11), Product-J and L shall be 200 (in Cell P15) and Product-J and N shall be 100 (shall appear in P17).

. Formula shall be in Column P.Same way it shall be for all equipments like 200L, 1200L and so on.Common equipment search for each shall be from Column J to 0.

[JohnTopley]

If we find a match in column L, do we only search columns L, M and N?

I repeat my request to add sample results (not explanation) to your posted workbook.

[rakeshgarg1977]

Search shall be from J to O irrespective of condition search column wether it is in J or K or L or M or N or O.

I tried to put some more details in attached sheet.