Summation across row using sumproduct/isnumber/match and named array(s)

[solved using an {array}]

Dataset description.

= Company ID and info is followed by the 'share type' for the top ten shareholders (type1 to type10)
= This is followed by the NAME of shareholder and their percentage of the company held for each of shareholders1-10

=Column AM is the important column and contains a formula kindly worked out by Spencer from this forum. I need to be able to make some adjustments to this formula as below.

This looks across the row and sums only those percentages whose corresponding name is on the 'qfii2' list on the sheet named 'qfii2'

However I need some ways to add filters.

FIRSTLY, I want it to sum those values ONLY if the individual (qfii2) shareholder percentage is <5%. Can you help me add this filter to the formula? (1)

SECONDLY, I wonder how I can add a filter so that it only adds the percentage if it not only matches the NAME on the qfii2 list but ALSO the shareholder (e.g. shareholder 2) is NOT of corresponding type (i.e. type2) of all those appearing on the 'nottypes' list (i.e. column A of sheet 5 - sharetypes). (2)

THIRDLY, the same as (1) I want to add a filter so that it only adds percentage when individual (qfii2) shareholder is >5% for another column of results.

FOURTHLY, I want to have a column that add the percentages along the row for ONLY those shareholders having a corresponding sharetype as those in the 'nottypes' list.

The sfjv2 list is simply another list of names, for which I want to perform a similar analysis. It can therefore be ignored.

To recap:

1) Sum percentages across row if name appears on QFII list AND corresponding percentage is <5%
2) Sum percentages across row if name appears on QFII list AND corresponding percentage is <5% AND corresponding typeN (n=1,...,10) for each qfii2 shareholder does NOT appear on the 'nottypes' list
3) Sum percentages across row if name appears on QFII list AND corresponding percentage is >5%
4) A column that sums percentages for all shareholders, regardless of name, provided that their corresponding type appears on the 'nottypes' list.

I am desperate for help on this issue I have been struggling with for literally months.

Thank you to the genius/guru who can help me to solve it.

I enclose a dummy sheet (as the dataset I am working with is quite large - this has only 3 rows rather than 20,000+).

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.