I need to get a column of data that equals a speci

Basically I have 4 columns on my data page and on a different page I have a list of the accounts. The reason why there are multiple lines of the same name on the Data page is because theyve contributed more than once on seperate dates.

I need to look at the accounts page where the Fund name equals Fund name on the Data page, SB equals SB on the Data page and SE equals SE on the Data page and find out how many contributors that Fund has and put them in an array.

In COLUMN D on the Accounts Page I then need to find where the SE is NOT a contributor of that fund, if they are return FALSE if they are not a contributor return TRUE.

In COLUMN E on the Accounts Page I need to find where the SE is the only contributor to that fund, if they are return TRUE if they are return FALSE.

In COLUMN E on the Accounts Page I need to find if the SE is one of many contributors, if they are return TRUE if they are return FALSE.

I have programming knowledge of java but it needs to be done in excel and I've done vba in the past but it was a long time ago!

ACCOUNT PAGE

DATA PAGE

A formula only option for you if I understood your question correctly.

The first logic check for column D:

=NOT(SUMPRODUCT((A2=$H$2:$H$14)*(C2=$K$2:$K$14))>0)

This formula will count the number of times Fund Name occurs in Column H when the SE's name Appears in Column K. So if this result is 0 they are never a contributor. Since you want never a contributor to be true, we need the NOT formula to change the 0 (False) to a 1 (True). If the persons name comes up 1 or more times as a contributor, lets say 2 times as an example, Excel treats any non 0 as True. So taking NOT(2) will result in 0 or False.

The second logic check for column E:

=SUMPRODUCT((A2=$H$2:$H$14)*(C2=$K$2:$K$14))=COUNTIF($H$2:$H$14,A2)

This formula is similar to the first. It starts by counting the number of times the person's name is a contributor to the account. It then counts the number of times the account appears in column H. IF they are equal, you know they are the sole contributor and TRUE will be displayed.

The third logic check for column F:

=AND(NOT(D2),NOT(E2))

This check is based on the previous two results. You need to know that they are A contributor, but that they are NOT the sole contributor. In other words your first logic check needs to be TRUE AND your second logic check needs to be FALSE.

This was all performed on the same sheet so you will need to update the address ranges. Fill out row 2 with the three formulas and copy down.

Proof of concept:

So how does a sumproduct work. When using sumproduct to do logical evaluations to get you a list, you need to remember that TRUE is >=1 and False is =0 for excel formulas. Not the same in VBA. SUMPRODUCT is also a bit of a cheat in that it does an ARRAY calculation inside the (). Lets look at the first sumproduct and step through it.

=SUMPRODUCT((A2=$H$2:$H$14)*(C2=$K$2:$K$14))

That first part (A2=$H$2:$H$14) could be enter as an array formula. It essentially looks like this:

Now if we take the second part (C2=$K$2:$K$14) which could also be entered as an array formula it essentially looks like this:

So now we have that * between the two array formulas which means the same row in each array formula get multiplied by the other. And the other very crucial step here is that the math act is what cause the array results to turn from TRUE/FALSE to 1/0. When you wind up multiplying the array formulas you are doing this:

You can think of the * as being an AND statement in this case, and an OR statement would be +.

Now with sumproduct there is a very important last step. It adds the resulting final array of numbers together to get a single number. So for the example case it would return 1. if the condition of the interior of the sumproduct been true on multiple occasions lets say three times. You would have seen three 1s in the final array. Which when the sum of the final array was taken means sumproduct would return 3.

So in summary, you can use sumproduct to count the number of times an multiple conditions are TRUE, you can sum values that meet the conditions if you multiply the column to be summed by the conditions, you can return the row number where a unique result is found by multiplying the conditions by row(range of cells), and possibly a few other things.