Org charts in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate the number of direct reports for a manager

Given a list of employees in column A and their managers in column B, which formula in cell D2 correctly counts how many direct reports the manager named in C2 has?

Google Sheets

A2:A10 contains employee names
B2:B10 contains their managers
C2 contains the manager name to check
D2 is where the formula goes

A =COUNTIF(A2:A10, C2)

B =COUNTIF(B2:B10, C2)

C =SUMIF(B2:B10, C2, A2:A10)

D =COUNTIFS(A2:A10, C2)

💡 Hint

Think about which column lists the managers for each employee.

The COUNTIF function counts how many times the manager's name in C2 appears in the managers column B2:B10, which equals the number of direct reports.

❓ Function Choice

intermediate

Choose the function to find an employee's manager

You have a table with employees in column A and their managers in column B. Which function would you use in cell D2 to find the manager of the employee named in C2?

Google Sheets

A2:A10 contains employee names
B2:B10 contains their managers
C2 contains the employee name to find the manager for
D2 is where the formula goes

A =INDEX(A2:A10, MATCH(C2, B2:B10, 0))

B =HLOOKUP(C2, A2:B10, 2, FALSE)

C =VLOOKUP(C2, A2:B10, 2, FALSE)

D =MATCH(C2, B2:B10, 0)

💡 Hint

You want to find a value in the first column and return a value from the second column.

VLOOKUP looks for the employee name in the first column and returns the corresponding manager from the second column.

❓ data_analysis

advanced

Identify the top-level manager in an org chart

Given a list of employees in column A and their managers in column B, which formula in cell D2 correctly identifies the top-level manager (the one who is not anyone's employee)?

Google Sheets

A2:A10 contains employee names
B2:B10 contains their managers
D2 is where the formula goes

A =INDEX(B2:B10, MATCH(TRUE, ISNA(MATCH(B2:B10, A2:A10, 0)), 0))

B =COUNTIF(B2:B10, "")

C =VLOOKUP("", A2:B10, 2, FALSE)

D =INDEX(A2:A10, MATCH(TRUE, ISNA(MATCH(A2:A10, B2:B10, 0)), 0))

💡 Hint

The top-level manager appears in the managers list but not in the employees list.

This formula finds the first manager in B2:B10 who does not appear in A2:A10, meaning they have no manager above them.

🎯 Scenario

advanced

Create a formula to display the full reporting chain for an employee

You want to create a formula that shows the full chain of managers for an employee listed in C2, using columns A (employees) and B (managers). Which approach is best to build this chain in Google Sheets?

Google Sheets

A2:A10 contains employee names
B2:B10 contains their managers
C2 contains the employee name
D2 is where the formula goes

A Use a recursive custom function (Apps Script) to loop through managers until top-level

B Use a single VLOOKUP to find the direct manager only

C Use FILTER to list all managers in one formula without recursion

D Use COUNTIF to count managers in the chain

💡 Hint

A chain requires repeated lookups until no manager is found.

Google Sheets formulas alone cannot loop recursively; a custom script is needed to build a full chain of managers.

📊 Formula Result

expert

Determine the total number of employees under a manager including indirect reports

Given columns A (employees) and B (managers), which formula or method correctly calculates the total number of employees reporting directly or indirectly to the manager named in C2?

Google Sheets

A2:A20 contains employee names
B2:B20 contains their managers
C2 contains the manager name
D2 is where the formula or method result goes

A =SUMPRODUCT(--(B2:B20=C2))

B =COUNTIF(B2:B20, C2)

C =COUNTIFS(A2:A20, C2)

D Use a recursive Apps Script function to count all employees under the manager

💡 Hint

Counting indirect reports requires checking multiple levels, not just direct reports.

Only a recursive script can traverse the hierarchy to count all direct and indirect reports; simple formulas count only direct reports.