Range of data in Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Narayanaswamy - 31 Jul 2006 16:42 GMT

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.

Reply to this Message

Andy - 31 Jul 2006 17:05 GMT

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.

>I want to count the number of occurances based on multiple queries.
> 1) Column A contains list of items (Let us say 25 items)
[quoted text clipped - 17 lines]
> information.
> Please let me know how to extract this data for analysis.

Reply to this Message