List All Pending Permits
I need to join all 'Permit Types' that have a status of 'Pending' into a single cell for the weekly summary.
=TEXTJOIN(", ", TRUE, IF(B2:B6="Pending", A2:A6, "")) How it works: This formula uses `TEXTJOIN` to concatenate text strings. The first argument `", "` specifies the delimiter (a comma followed by a space) to separate each permit type. The second argument `TRUE` tells `TEXTJOIN` to ignore any empty cells that result from the `IF` function. The `IF(B2:B6="Pending", A2:A6, "")` part is an array formula that checks each cell in the 'Status' range (B2:B6). If a status is 'Pending', it returns the corresponding 'Permit Type' from column A (A2:A6); otherwise, it returns an empty string. `TEXTJOIN` then takes this array of permit types (and empty strings) and joins only the non-empty ones, separated by the specified delimiter.
Data Setup
| Permit Type | Status |
|---|---|
| Building Permit | Pending |
| Electrical Permit | Approved |
| Plumbing Permit | Pending |
| Zoning Variance | Rejected |
| Fire Safety Permit | Pending |
Step-by-Step Guide
Ensure your permit data is organized with 'Permit Type' in one column (e.g., Column A) and 'Status' in another (e.g., Column B).
Select the cell where you want the combined list of pending permits to appear (e.g., C2).
Enter the formula: `=TEXTJOIN(", ", TRUE, IF(B2:B6="Pending", A2:A6, ""))`
Press Enter. If you are using an older version of Excel that doesn't support dynamic arrays, you might need to press CTRL+SHIFT+ENTER to enter it as an array formula.
Explore More
Aggregating Project Costs by Completion Status
As a Project Manager, I oversee multiple projects, each with varying costs and statuses. I need to quickly determine the total cost of all 'Completed' projects to reconcile budgets, or the total cost of 'In Progress' projects to forecast remaining expenditures. Manually filtering and summing is inefficient.
Assembling Project Task Dependency Chains
A project manager needs to quickly create a visual representation of a sequence of dependent tasks for a project phase. They want to combine the task names into a single, easy-to-read string, separated by an arrow (e.g., ' -> '), to clearly show the workflow and dependencies to team members or stakeholders. Manually typing this out for many tasks is inefficient and prone to errors.
Determine Project Deadline from Start Date
I need to find the exact calendar date for a project deadline that is 45 working days from the start date.