How to calculate the average cost of a particular category from so many models

[PRADEEPB270]

I have a file,in a sheet,there are 36 models with category wise cost.Say, model-SPRFDRKCC and category wise cost-CKD,LOCAL,E.Oil etc.Now,I want the average cost of all the model with each category.For an example,I want to know the average cost of CKD of all the model in the month of Nov,11.Can it be solve from an excel formula?The average cost should be come as I have mentioned in my file on cell C366.

Please refer attached file for more clarification.

Regards

Pradeep Kumar Gupta

[Marcol]

Your layout is far from ideal, try this in C366

Please Login or Register  to view this content.

Drag/Fill Down

This will work for all versions of Excel, as you are using 2007 or above you could use a combination of SUMIFS() and COUNTIFS(), I only have 2003 available at the moment.

[royUK]

If all the data was set out in a Table with a column for categories then a PivotTable would do the job

[PRADEEPB270]

Thanks for the very nice solution.

Regards

[Ron Coderre]

If the same date is used throughout the data, try something like this:

Please Login or Register  to view this content.

Is that something you can work with?

[Marcol]

You really would be better reorganising your worksheet.
Then the formulae become much simpler, or indeed you could use a pivot table as Roy suggests.

See if you can work with this.

[PRADEEPB270]

Thanks for providing the best idea to solve the problem.

Regards

[PRADEEPB270]

Excellent solution.Thanks for suggestion.

Regards