Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Family Tree in Excel

Count Unique from multiple columns (One criteria for multiple columns based on two conditions)

In this example Family Tree, I will show how we can count how many times it appears certain criteria in multiple columns if you have multiple conditions. The situation in the picture below you will see a number of families that belong to the names of people for a particular family. We want to count how many basic families in a family tree. In the picture below you will see a number of family trees that branch out to the members.

Figure 1.
Branching family members

On the picture below you see the names of family members (Family Tree) copied into Excel table.


Figure 2.
Family Tree in Excel Table

Our quest or task is, based on data from multiple columns count how many basic families on the list. Note that this task can be solved in several ways. I will show two ways of solving problems.

FIRST WAY

How to count unique family from the Family Tree

In column 'H', we want to show the number as a sub-families of the basic families if our criterion is the main carrier of the family.
Take for example the holder of families, "Jack Craig." His family has branches in several families (descendants). Our quest is using Excel formulas to find out how many of his families in the offspring. For a concrete example of "Jack Craig" has 4 family descendants.

The formula in the 'H2' is the next cell. Below mentioned formula matched two columns (A and D) and on the basis of those returns the number of unique data.

=SUMPRODUCT((($D$2:$D$24=G2))/COUNTIFS($D$2:$D$24;$D$2:$D$24&"";$A$2:$A$24;$A$2:$A$24&""))

ANOTHER WAY

Count Unique Family from multiple columns if match criteria

If we want to solve the problem in a different way, then we can add an extra column, which of course we can hide. In the picture below you can see three extra columns.

In column 'J' combine data from two columns of the original data. These are the columns 'D' and 'A'.

The formula in the 'J2' cell: (using this formula, we narrowed the type of information the search.)

=D2&"-"&A2

Array Formula in the 'K2' cell: (this formula is generally satisfied our needs. The formula is unique data returned from the J column. In this column, you can note the number of families belonging to certain holders of families who is the criteria in the column 'G '. This column is not enough for the solution of our task, because the names of family members contain a number, which is in the way in the search for a criterion.

=IFERROR(INDEX($J$2:$J$24;MATCH(0;COUNTIF($K$1:K1;$J$2:$J$24);0));"")

The formula in the 'L2' cell: (this formula extracts the names of persons from column 'K'. In fact this formula removes the character minus, and the number behind it, so that gives us the name of a family member)

=LEFT(K2;FIND("-";K2)-1)

Figure 3.
Counting families descendants in the family tree

Finally, the formula in 'H2' cell is the following

=COUNTIF($L$2:$L$14;G2)

This formula above, counts the number of times the criteria from column 'G' appears in the column "L" and returns us the total number of families (Family Tree) belonging to the holder (criteria).