[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+).
Bookmarks