Calculating No. of Options Sold (more complicated than it sounds, trust)

[pand23]

Hello, I've tried searching for a solution, but to no avail.

Below is a sales report of some Nike sneakers.

Product is the item down to size level.

Master is the Option Count. For example, a t-shirt, 400280 comes in 3 sizes, S, M and L. Hence why product is 400280-001 (small), 002 (medium) and 003 (large).

However it remains 1 option (not 3).

There are 7 options below with 12 units sold in total.
How would I express a formula within a Pivot Table as a calculated field to show the number or sum of options sold?
I have tried to come up with a solution, but it is too advanced for me.

I know the answer is 4 as there are sales against 4 unique "MASTER" references, but I don't know how to write a formula to express this. Would it be even possible as a calculated field in a Pivot? Thank you


[TABLE="width: 724"]
<colgroup><col><col><col><col span="7"></colgroup><tbody>[TR]
[TD][TABLE="width: 724"]
<colgroup><col><col><col><col span="7"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brand[/TD]
[TD]Product[/TD]
[TD]MASTER[/TD]
[TD]MASTER2[/TD]
[TD]Option Count[/TD]
[TD]Units Sold[/TD]
[TD][/TD]
[TD]Options Sold[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400281-005[/TD]
[TD]400281[/TD]
[TD]400281[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400281-004[/TD]
[TD]400281[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400281-003[/TD]
[TD]400281[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400281-002[/TD]
[TD]400281[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400281-001[/TD]
[TD]400281[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400280-003[/TD]
[TD]400280[/TD]
[TD]400280[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400280-002[/TD]
[TD]400280[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400280-001[/TD]
[TD]400280[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400248-001[/TD]
[TD]400248[/TD]
[TD]400248[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400247-001[/TD]
[TD]400247[/TD]
[TD]400247[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400246-001[/TD]
[TD]400246[/TD]
[TD]400246[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400245-005[/TD]
[TD]400245[/TD]
[TD]400245[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400245-004[/TD]
[TD]400245[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400245-003[/TD]
[TD]400245[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400245-002[/TD]
[TD]400245[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400245-001[/TD]
[TD]400245[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nike[/TD]
[TD]400244-001[/TD]
[TD]400244[/TD]
[TD]400244[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]