How to sum up the 2 different group's Materials

[PRADEEPB270]

I have a file with so many 'Material' in column 'B'.These material have in 2 category,i.e.1-SPTR ( All materials not end with 'S') and ZSPR (All Materials end with 'S' only).
Now,I want to sum up ( Col.F ) the 'Material' ( Col.B ) according to 'Material Group' ( Col.D) in column 'I' and 'J' ( Both group separately ).How can we sum up the total of these categories? Please suggest an excel formula and VBA codes,which is the best, to find the result .

For more clarification,please refer my attach file.

[Rudi]

Do you have many sheets that need to be summed by the material group, or is it just one or two sheets?

[PRADEEPB270]

I have only one sheet.

[Rudi]

OK. Then all you need is some formulas to calculate those values.
I will send you you sample workbook back with the necessary formulas soon.

[Rudi]

Workbook attached...

SLM1.xlsx

[Rudi]

Please note:
The formulas are based on arrays.
If you edit or copy the formulas you must press CTRL+SHIFT+ENTER to confirm them, else they will not calculate properly.
You cannot type the curly brackets in manually { }. These are added when you press CTRL+SHIFT+ENTER.

[PRADEEPB270]

Glad to find the problem sort out formula.Thanks a lot Rudi for your nice cooperation.

[macropod]

Cross-posted (and answered) at: http://www.msofficeforums.com/excel-pro ... p-2-a.html" onclick="window.open(this.href);return false;
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184" onclick="window.open(this.href);return false;