Grouping Values based on a criteria in a Column and MUltiple Rows over a range

Hi,

We use the INDEX and MATCH functions to return the intersection of a row and a column (mix of VLOOKUP and HLOOKUP). My requirement is that there are multiple entries for the same criteria selected and I need to perform an aggregate function on all the values for a criteria. I have added a Test1.xls sheet which actually clearly explains out my requirement. Kindly request someone to help me out devise a formula for my requirement.

Hi Jaac,

Thanks for your prompt reply. I cannot use Pivot Tables because the output that I have shown you is a fairly condensed version of what is required. I need a formula in a cell to carry out that function because I am using the result in another long expression consisting of other formulas.

Countifs might be an option

See attachment Test1 Shadrack.xls

Hi Shadrack,

Thanks for your reply. The approach that has been presented by you unfortunately does not meet my requirement. To clear out my requirement further with respect to your answer let me explain the following.
The formula in cell B26 of Test1 Shadrack.xls is =COUNTIFS($B$2:$B$13,$A26,C$2:C$13,">=1"). What I need is that the range C$2:C$13 in your formula should be dynamically arrived upon by matching the month name Nov-12 from the above reference table. This seems to be the hard part. Request you to look for a solution regarding this.

Hi Shadrack,

I have modified my excel sheet to use your solution. Thanks a lot for the reply. But just to point out that my original requirement is very well explained in this thread http://http://www.excelforum.com/excel-gene...e-results.html. I am waiting for a reply on this thread to know as what could be the solution to my original problem.

Still looking into your modified file. Before then, here is a fellow-up of yesterday's answer.

Test2 Shadrack.xls

Report if problem is solved.