Family Tree in Excel
Count Unique From Multiple Columns
(One criteria for multiple columns based on two conditions)
In this Excel 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.
On the picture below you see the Names of Family Members (Family Tree) copied into Excel range/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.
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.
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 columns, 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.)
Array Formula in the 'K2' cell: (this formula is generally satisfy our needs. The formula is unique data returned from the 'J' column. In this column you can note 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.
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)
Finally, the formula in 'H2' cell is the following
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).