Process design is like constructing a well-organized system—it’s all about using the right tools in the right places. When you know which formulas to use and where to apply them, you can transform workflows, improve accuracy, and save time. Here’s a guide to five must-know formulas and how they can streamline your processes.
1. ARRAYFORMULA – Automating Repetitive Calculations
What it does: Dynamically applies a formula to an entire range of data, reducing manual work.
ARRAYFORMULA example
Imagine managing a project with dozens of tasks. Use ARRAYFORMULA to calculate completion rates automatically as new task data is added.
Spreadsheet before using ARRAYFORMULA
|
A |
B |
C |
D |
|
Task Name |
Total Subtasks |
Completed Subtasks |
Completion Rate (%) |
|
Task A |
5 |
3 |
▶️ MANUAL INPUT ◀️ |
|
Task B |
4 |
2 |
▶️ MANUAL INPUT ◀️ |
Spreadsheet after using ARRAYFORMULA:
|
Task Name |
Total Subtasks |
Completed Subtasks |
Completion Rate (%) |
|
Task A |
5 |
3 |
▶️ =ARRAYFORMULA(C2:C / B2:B * 100) ◀️ |
|
Task B |
4 |
2 |
🤖 Automatically filled 🤖 |
Where It’s Useful
- Inventory tracking
- Task management
- Workflows with frequent updates
2. IFS – Simplifying Complex Decisions
What it does: Evaluates multiple conditions and returns specific results based on the first condition met.
IFS Example
A lead scoring system prioritizes prospects using the IFS formula.
=IFS(B2>80, "Hot", B2>50, "Warm", B2<=50, "Cold")
|
Lead Scoring Table: |
||
|
Lead Name |
Engagement Score |
Priority |
|
Lead A |
85 |
Hot |
|
Lead B |
65 |
Warm |
|
Lead C |
40 |
Cold |
Where it’s useful:
- Decision-making systems
- Task prioritization
- Data categorization
3. QUERY – Advanced Data Analysis
What it does: Filters, sorts, and organizes data dynamically based on your criteria.
QUERY Example
A sales manager uses QUERY to pull performance data for a specific region and time period.
Sales Data Table
|
A |
B |
C |
D |
|
Region |
Sales Rep |
Sales Amount |
Quarter |
|
North |
Alice |
50,000 |
Q1 |
|
South |
Bob |
45,000 |
Q1 |
|
North |
Alice |
70,000 |
Q2 |
Use To filter “North” region sales for Q1
=QUERY(A1:D, "SELECT B, C WHERE A='North' AND D='Q1'")
|
Result Table: |
|
|
Sales Rep |
Sales Amount |
|
Alice |
50,000 |
Where it’s useful:
- Dashboards
- Reporting systems
- Performance tracking
4. VLOOKUP – Connecting Data Across Tables
What it does: Retrieves data from another table based on a unique identifier, ensuring consistency across datasets.
VLOOKUP example
A customer database pulls client contact information into an invoice template using VLOOKUP.
Customer Database:
| A | B | C | D |
| Client ID | Client Name | Contact Email | |
| 101 | Alice Smith | alice [at] example.com | |
| 102 | Bob Jones | bob [at] example.com |
Invoice Template:
|
A |
B |
C |
D |
|
Invoice ID |
Client ID |
Client Name |
Contact Email |
|
INV001 |
101 |
=VLOOKUP(B2, CustomerDatabase!A:C, 2, FALSE) |
=VLOOKUP(B2, CustomerDatabase!A:C, 3, FALSE) |
Where it’s useful:
- Linking datasets
- Generating reports
- Automating forms
5. SUMIF – Aggregating Data by Criteria
What it does: Adds values that meet specific criteria, simplifying your analysis.
SUMIF example
A marketing team uses SUMIF to calculate total ad spend by campaign type.
|
Campaign Data Table: |
|
|
Campaign Type |
Ad Spend ($) |
|
Social Media |
1,200 |
|
Search Ads |
2,500 |
|
Social Media |
800 |
Formula to Calculate Total Ad Spend for Social Media
=SUMIF(A2:A, "Social Media", B2:B)
|
Result: |
|
|
Campaign Type |
Total Ad Spend ($) |
|
Social Media |
2,000 |
Build Smarter Workflows with Advanced Formulas
These five formulas are essential tools for any process designer. They help you automate tasks, connect data, and analyze information with greater accuracy and speed. By integrating these formulas into your workflows, you can build efficient, scalable, and adaptable systems.
That’s it! Start experimenting with these formulas today to transform your process design and unlock the full potential of your workflows.
English 
Español 
Português do Brasil 
Français