Get a percentage based on multiple conditions

[Matt]

I would like to determine a percentage in Excel that is based on
multiple conditions. This is for a serious a products on different
displays and the quantity on those displays. I need to know out of
all the stores surveyed, what percentage of those stores had a lobby
display with more than 150 cases. If I had a small set of data I'm
sure I could just pivot the data a few ways and be done, but I have
over 50,000 rows. See the sample set of data below for more detail.

Store Display Product Qty
------- -------------- -------------- ----------
123 Lobby Soda Cans 50
123 Aisle Soda Cans 75
123 Perimeter Soda Cans 75
123 Lobby 2 liter 120
123 Aisle 2 liter 200
123 Perimeter 2 liter 50
123 Lobby 20 oz 20
123 Aisle 20 oz 60
123 Perimeter 20 oz 0
456 Lobby Soda Cans 100
456 Aisle Soda Cans 50
456 Perimeter Soda Cans 50
456 Lobby 2 liter 25
456 Aisle 2 liter 150
456 Perimeter 2 liter 25
456 Lobby 20 oz 20
456 Aisle 20 oz 60
456 Perimeter 20 oz 0
789 Lobby Soda Cans 40
789 Aisle Soda Cans 100
789 Perimeter Soda Cans 20
789 Lobby 2 liter 80
789 Aisle 2 liter 200
789 Perimeter 2 liter 50
789 Lobby 20 oz 40
789 Aisle 20 oz 60
789 Perimeter 20 oz 0

From this sample data, you would determine that out of the 3 stores

only 2 of them (66%) have a Lobby display with more than 150 cases. In
Excel 2003, how I can I do this? I cannot wrap my head around it.
Thank you in advance for your assistance.

 

[Scott]

I would like to determine a percentage in Excel that is based on
multiple conditions. This is for a serious a products on different
displays and the quantity on those displays. I need to know out of
all the stores surveyed, what percentage of those stores had a lobby
display with more than 150 cases. If I had a small set of data I'm
sure I could just pivot the data a few ways and be done, but I have
over 50,000 rows. See the sample set of data below for more detail.

Store Display Product Qty
------- -------------- -------------- ----------
123 Lobby Soda Cans 50
123 Aisle Soda Cans 75
123 Perimeter Soda Cans 75
123 Lobby 2 liter 120
123 Aisle 2 liter 200
123 Perimeter 2 liter 50
123 Lobby 20 oz 20
123 Aisle 20 oz 60
123 Perimeter 20 oz 0
456 Lobby Soda Cans 100
456 Aisle Soda Cans 50
456 Perimeter Soda Cans 50
456 Lobby 2 liter 25
456 Aisle 2 liter 150
456 Perimeter 2 liter 25
456 Lobby 20 oz 20
456 Aisle 20 oz 60
456 Perimeter 20 oz 0
789 Lobby Soda Cans 40
789 Aisle Soda Cans 100
789 Perimeter Soda Cans 20
789 Lobby 2 liter 80
789 Aisle 2 liter 200
789 Perimeter 2 liter 50
789 Lobby 20 oz 40
789 Aisle 20 oz 60
789 Perimeter 20 oz 0


only 2 of them (66%) have a Lobby display with more than 150 cases. In
Excel 2003, how I can I do this? I cannot wrap my head around it.
Thank you in advance for your assistance.

You might try the following formula:
=COUNTIF(D250000,">=150")/COUNT(D250000)

Scott

 

[Scott]

You might try the following formula:
=COUNTIF(D250000,">=150")/COUNT(D250000)

Scott

Ok, Ignore me.. I didn't read.

 

[Matt]

Ok, Ignore me.. I didn't read. - Hide quoted text -

- Show quoted text -

Well, that may help in a small way, it will give me an idea of what
I'm working with, but unfortunately that still isn't the solution. I
haven't been able to figure out a way to do this...

 

[daxmiller]

I would like to determine a percentage in Excel that is based on
multiple conditions. This is for a serious a products on different
displays and the quantity on those displays. I need to know out of
all the stores surveyed, what percentage of those stores had a lobby
display with more than 150 cases. If I had a small set of data I'm
sure I could just pivot the data a few ways and be done, but I have
over 50,000 rows. See the sample set of data below for more detail.

Store Display Product Qty
------- -------------- -------------- ----------
123 Lobby Soda Cans 50
123 Aisle Soda Cans 75
123 Perimeter Soda Cans 75
123 Lobby 2 liter 120
123 Aisle 2 liter 200
123 Perimeter 2 liter 50
123 Lobby 20 oz 20
123 Aisle 20 oz 60
123 Perimeter 20 oz 0
456 Lobby Soda Cans 100
456 Aisle Soda Cans 50
456 Perimeter Soda Cans 50
456 Lobby 2 liter 25
456 Aisle 2 liter 150
456 Perimeter 2 liter 25
456 Lobby 20 oz 20
456 Aisle 20 oz 60
456 Perimeter 20 oz 0
789 Lobby Soda Cans 40
789 Aisle Soda Cans 100
789 Perimeter Soda Cans 20
789 Lobby 2 liter 80
789 Aisle 2 liter 200
789 Perimeter 2 liter 50
789 Lobby 20 oz 40
789 Aisle 20 oz 60
789 Perimeter 20 oz 0


only 2 of them (66%) have a Lobby display with more than 150 cases. In
Excel 2003, how I can I do this? I cannot wrap my head around it.
Thank you in advance for your assistance.

Enter the following as an array formula i.e. ... Ctrl Shft Enter,
rather than the usual ... Enter:

=COUNT(IF($B2:$B50000="Lobby",IF($D2:$D50000=150,$D2:$D50000))) /
COUNT($D$2:$D$50000)