Range of data in Excel

[Narayanaswamy]

I want to count the number of occurances based on multiple queries.
1) Column A contains list of items (Let us say 25 items)
2) Column B contains the grades (1,2,3,4). matching against each of the
listed item. Let us assume that grade1 costs $4, grade2 costs $8, grade3
costs $12 and grade4 costs $16.
3) Column C contains the percentage at which each of these items are sold.
Let us say the least percentage of an item sold is 78% and highest percentage
at which an item sold is 121%.

I am trying to come out with a matrix of how many
grade1/grade2/grade3/grade4 items sold with percentages <80, between 80-100,
between 100-120 and >120

To put that in a simple question,
How many of grade3 items sold between the percentage 80 to 100?

I have tried many formulae, but not successful to capture this information.
Please let me know how to extract this data for analysis.

 

[Guest]

Hi

Try something like this:
=SUMPRODUCT((A2:A40="a")*(B2:B40=1)*(C2:C40>0.8)*(C2:C40<1))
This will give you a count of how many items 'a', grade 1, were sold between
80 and 100 per cent.

Hope this helps.
Andy.