List all scenarios that match 2 criteria

Hi,

I am struggling a bit with the following issue:
Say there are 6 shareholders in 1 company, each with their respective ownership and voting rights.
Now I want to list all scenarios whereby there are at least 4 shareholders in favor of a vote, and that the combined ownership of the shareholders in favor is at least 50%.
How can that be done? Can this be done using VBA?

Example:
Shareholder 1 30 %
Shareholder 2 30 %
Shareholder 3 15 %
Shareholder 4 13 %
Shareholder 5 10 %
Shareholder 6 2 %

In this case, if Shareholder 1, 2, 3 and 4 are in favor, there are 4 shareholders in favor, and the combined ownership is 88%, so both criteria are fulfilled.
However, if Shareholder 2, 3, 4, 5 and 6 are in favor, both criteria are also fulfilled (5 shareholders in favor and total ownership is 70%).

Here is an example by setting 1's (in favor of vote) and 0's (against vote).
In this case, scenario C matches both criteria.
But instead of entering all possible scenarios manually, I want to automate this using formulas or vba. Is this possible?
Ownership % Alternatives
A B C D E F
Shareholder 1 30 % 1 1 1 1 1 0
Shareholder 2 30 % 1 1 1 0 0 1
Shareholder 3 15 % 0 1 1 1 0 1
Shareholder 4 13 % 0 0 1 1 1 1
Shareholder 5 10 % 0 0 0 0 1 0
Shareholder 6 2 % 0 0 0 0 0 0
1
Percentage 60 %75 % 88 % 58 % 53 % 58 %
Number of Shareholders 2 3 4 3 3 3



Voting rule: % HEADS
50 % 4

Anyone have any thoughts on how this can be done please?

Try:-

Please Login or Register  to view this content.

Hi MickG, thank you for your reply.
This formula works as is, however, I have not listed all possible combinations/scenarios.
If I add more shareholders, I still want to be able to list all possible combinations.
I am not really looking for a "count" function. Rather, I want to display all possible combinations that match the criteria.
For example: A B C D
Shareholder 1 1 1 1 1
Shareholder 2 1 1 1 1
Shareholder 3 1 1 1 1
Shareholder 4 1 1 1 0
Shareholder 5 1 1 0 1
Shareholder 6 1 0 0 0

Where A, B, C,... are the scenarios that match the criteria
Set a "1" for "in favor" and "0" for "Against".

The following cases should not be shown:
Shareholder 1 0 0 1 0
Shareholder 2 0 0 1 0
Shareholder 3 0 0 0 1
Shareholder 4 0 0 1 1
Shareholder 5 1 0 0 1
Shareholder 6 1 0 0 1