VBA conditional scoring + average

[jsfon]

Hello all, I would like to create a macro considering the following:

Sheet 1 (dynamic named range for different fruits)
 
ColumnA  | ColumnB  | ColumnC
Banana                  
Apple          
...
 
 
Sheet 2  (montly evaluation by different persons and on different criteria)
 
ColumnA(fruit) | ColumnB(month) | ColumnC(year) | ColumnD(taste) | ColumnE(colour)
banana         |    june        |       2011      |    good      |     poor
banana         |    june        |       2011      |    poor      |     good
Apple          |    june        |       2011       |  avarage     |     poor
banana         |    july        |       2011      |    good      |     average

Now If I define in sheet3 the month and year for my report ( A1=June and A2=2011), the macro should display in sheet 1 the average score for each fruit in the different criteria (columnB = ctaste, columnC = colour) based on: "poor"=1, "average"=2 and "good"=3.

So in this example (june 2011) the result would be:

Sheet 1 (dynamic named range for different fruits that is expandeble as new fruits are added from time to time)
 
ColumnA | ColumnB | ColumnC
Banana       2         2
Apple        2         1
cheries    'empty    'empty
...

Thank you in advance

 

[jsfon]

Can anyone please help?

 

[CharlesChuckieCharles]

so you want to count the Poors and goods for a specific piece of fruit and create an average impression on average colour was poor

what is the basis of the average?

 

[jsfon]

I want to count the poors(= 1point), average (=2points) and goods (=3points) and then average the score in the diferent fields (color, taste); the basis is the number of evaluations entered in sheet 2 for that specific fruit (and in relation to the period disired: in this example June 2011).

The macro should go through the list of fruits in sheet 1, compare with the evaluations entered by row in sheet 2 (and criteria June 2011) and then perform the calculations and display the result in column B,C in sheet1

 

[jsfon]

I'm really lost here, can you help?