One value with multiple values within, need to calculate as a percentage

[jimmysumpter]

Hi guys,

My excel skills are somewhat limited (compared to many on here) so I hope the following makes sense!

I have a list of group ID codes, which contain 1 or more product codes within them.

Some product codes contain an "alternative" value (the actual value is irrelevant) and others do not. See example below

Group ID	Product Code	Alternative
56381	240027	160380
56381	240028
56381	240029	160380
56381	240030

<tbody>
</tbody>

Ultimately what I need to achieve is a percentage of how many product codes, within each group ID, contain an alternative.

So for the example above, group ID 56381 would have 50% codes with alternatives.

By counting the occurrences of a group ID and whether 1 particular code has an alternative I have calculated individual percentages against the size of the group e.g. product code 240027 (given a value of 1) divided by the total number of codes in the group (4), returns 0.25. Obviously doing this across the whole group would give my result (0.5 or 50%)

However some groups contain over 100 codes and the spreadsheet is 40K rows!

Any help is much appreciated, again apologies if it doesn't make sense!

Jimmy

 

[oldbrewer]

Group ID	Product Code	Alternative
56381	240027	160380
56381	240028
56381	240029	160380
56381	240030
56384	240031	4
56384	240032
56384	240033
56384	240034	5
56384	240035
56384	240036
56384	240037
56384	240038	6
56384	240039
56384	240040
					Group ID	prodcodes	alternatives	%
					56381	4	2	50
					56384	10	3	30
		formula in cell with prodcodes = 4
		=SUMPRODUCT(($A$2:$A$15=F20)*1)
		formula in cell with alternatives = 2
		=SUMPRODUCT(($A$2:$A$15=F20)*($C$2:$C$15<>""))

<colgroup><col><col><col><col span="4"><col><col span="3"></colgroup><tbody>
</tbody>

 

[oldbrewer]

maybe first use a pivot table just to identify all prodids and use that as left column in the table i did for you

 

[oldbrewer]

typo - I meant group ids

 

[jimmysumpter]

Absolutely brilliant!!

Thanks so much oldbrewer!

 

[oldbrewer]

Glad to help mate - I am retired and try to keep the grey cells working