Formulas Reference

All formulas needed for the Arkade-Sommerfest work schedule system. Copy and paste as needed!

Prerequisites

Sheet name: “Schedule Overview” (main view)
Sheet name: “Registrations” (form responses)
Column C in Registrations: Area/Bereich
Column D in Registrations: Time Slot/Zeitslot
Column B in Registrations: Name

Basic Formulas

1. Simple Count (Shows: “2”)

Use in: Cell B2 (and copy to all data cells)

=COUNTIFS(Registrations!$C:$C,$A2,Registrations!$D:$D,B$1)

What it does: Counts how many people registered for this area/time combination

Example output: 2, 1, 0

2. Count with Total (Shows: “2/2”)

Use in: Cell B2 (and copy to all data cells)

=COUNTIFS(Registrations!$C:$C,$A2,Registrations!$D:$D,B$1)&"/2"

What it does: Shows current count and required count

Example output: 2/2, 1/2, 0/2

3. Count with Names (Shows: “2/2: Max, Anna”)

Use in: Cell B2 (and copy to all data cells)

=IF(COUNTIFS(Registrations!$C:$C,$A2,Registrations!$D:$D,B$1)>0,
  COUNTIFS(Registrations!$C:$C,$A2,Registrations!$D:$D,B$1)&"/2: "&
  JOIN(", ",FILTER(Registrations!$B:$B,(Registrations!$C:$C=$A2)*(Registrations!$D:$D=B$1))),
  "0/2")

What it does: Shows count AND names of registered people

Example output:

2/2: Max Müller, Anna Schmidt
1/2: John Doe
0/2

Conditional Formatting Formulas

Apply to range: B2:F8 (or your data range)

Rule 1: Green Background (Fully Staffed)

When: 2 people registered

Formula:

=COUNTIFS(Registrations!$C:$C,$A2,Registrations!$D:$D,B$1)=2

Formatting:

Background color: #34A853 (Green)
Text color: #FFFFFF (White) or #137333 (Dark Green)

Rule 2: Yellow Background (Partially Staffed)

When: 1 person registered

Formula:

=COUNTIFS(Registrations!$C:$C,$A2,Registrations!$D:$D,B$1)=1

Formatting:

Background color: #FBBC04 (Yellow)
Text color: #000000 (Black)

Rule 3: Red Background (Empty)

When: 0 people registered

Formula:

=COUNTIFS(Registrations!$C:$C,$A2,Registrations!$D:$D,B$1)=0

Formatting:

Background color: #EA4335 (Red)
Text color: #FFFFFF (White)

Rule 4: Orange Background (Overstaffed) - Optional

When: More than 2 people registered

Formula:

=COUNTIFS(Registrations!$C:$C,$A2,Registrations!$D:$D,B$1)>2

Formatting:

Background color: #FF9900 (Orange)
Text color: #000000 (Black)

Advanced Formulas

5. Names Only (for separate column)

Use in: Column G or separate sheet

=JOIN(", ",FILTER(Registrations!$B:$B,(Registrations!$C:$C=$A2)*(Registrations!$D:$D=B$1)))

What it does: Lists only the names

Example output: Max Müller, Anna Schmidt, John Doe

6. Email Addresses (for contact list)

Use in: Separate helper sheet

=JOIN("; ",FILTER(Registrations!$B:$B&" <"&Registrations!$A:$A&">",
  (Registrations!$C:$C=$A2)*(Registrations!$D:$D=B$1)))

Assumes: Column A in Registrations has email addresses

Example output: Max Müller <max@example.com>; Anna Schmidt <anna@example.com>

7. Status Text (for status column)

Use in: Helper column

=IF(COUNTIFS(Registrations!$C:$C,$A2,Registrations!$D:$D,B$1)=2,"Voll besetzt",
  IF(COUNTIFS(Registrations!$C:$C,$A2,Registrations!$D:$D,B$1)=1,"1 Person fehlt",
  IF(COUNTIFS(Registrations!$C:$C,$A2,Registrations!$D:$D,B$1)>2,"Überbesetzt","Frei")))

Example output: Voll besetzt, 1 Person fehlt, Frei, Überbesetzt

8. Percentage Full (for charts)

Use in: Helper sheet for statistics

=COUNTIFS(Registrations!$C:$C,$A2,Registrations!$D:$D,B$1)/2*100&"%"

Example output: 100%, 50%, 0%, 150%

Statistics Formulas

9. Total Registrations per Area

Use in: Summary sheet

=COUNTIF(Registrations!$C:$C,"Getränkeausgabe")

Replace: “Getränkeausgabe” with your area name

10. Total Registrations per Time Slot

Use in: Summary sheet

=COUNTIF(Registrations!$D:$D,"11:00-13:00")

Replace: “11:00-13:00” with your time slot

11. Overall Percentage Filled

Use in: Dashboard cell

=COUNTA(Registrations!$B:$B)/28*100&"%"

Adjust: Total valid person-slots = 28

BONKASSE: 2 shifts × 2 people = 4
Getränkeausgabe: 3 shifts × 2 people = 6
Kaffeebar: 3 shifts × 2 people = 6
Springer/Aufräumer: 3 shifts × 2 people = 6
Abbau: 1 shift × 6 people = 6
(Aufbau excluded - team TBD)

12. Empty Slots Count

Use in: Dashboard cell

=COUNTIF(B2:F8,"0/2")

Adjust: B2:F8 to your data range

Helper Formulas

13. Check for Name Match Errors

Use in: Validation sheet

Check if Form area names match Sheet area names:

=IF(COUNTIF('Schedule Overview'!$A:$A,C2)=0,"⚠️ Area name mismatch: "&C2,"✓")

Place in a column next to Registrations data

14. Highlight Duplicates

Use in: Registrations sheet conditional formatting

Check if someone registered multiple times for same slot:

=COUNTIFS($B:$B,$B2,$C:$C,$C2,$D:$D,$D2)>1

Formatting: Light orange background

15. Last Updated Timestamp

Use in: Cell A1 or header

="Letztes Update: "&TEXT(NOW(),"DD.MM.YYYY HH:MM")

Note: Only updates when sheet recalculates

Quick Reference Table

Formula Type	Use Case	Complexity	Recommended
Formula #2	Standard count display	Easy	✅ Yes
Formula #3	Count + names	Medium	✅ Yes (if space allows)
Formula #1	Minimal display	Easy	For small screens
Formula #7	German status text	Medium	Optional
Formula #11	Overall progress	Easy	✅ Yes (dashboard)
Formula #14	Prevent duplicates	Medium	✅ Yes (validation)

Copy-Paste Instructions

For Cell B2 (First Data Cell):

Click cell B2
Paste one of formulas #1, #2, or #3
Press Enter
Click cell B2 again
Copy (Ctrl+C)
Select range B2:F8 (all data cells)
Paste (Ctrl+V)

For Conditional Formatting:

Select range B2:F8
Format > Conditional formatting
Click “Add another rule” (4 times for 4 rules)
For each rule:
- Select “Custom formula is”
- Paste the formula
- Set formatting colors
- Click “Done”

Troubleshooting

Formula shows #REF! error

Problem: Sheet name doesn’t match

Fix: Make sure sheet is named “Registrations” exactly (no spaces before/after)

Formula shows #N/A error

Problem: FILTER function found no matches

Fix: Wrap FILTER in IFERROR:

=IFERROR(FILTER(...), "")

Conditional formatting not working

Problem: Formula syntax or range issue

Fix:

Use $A2 for row reference (no $ before number)
Use B$1 for column reference ($ before number)
Check that formula starts with =

Names not showing correctly

Problem: Column references wrong

Fix: Verify:

Column B = Names (in Registrations sheet)
Column C = Areas (in Registrations sheet)
Column D = Time Slots (in Registrations sheet)

Testing Your Formulas

After pasting formulas:

✅ Cell shows “0/2” (before any registrations)
✅ Cell is RED
✅ Submit test form entry
✅ Cell shows “1/2” with name
✅ Cell turns YELLOW
✅ Submit another entry for same slot
✅ Cell shows “2/2” with both names
✅ Cell turns GREEN
✅ Delete test entries
✅ Cell returns to “0/2” and RED

Pro Tips

Test in one cell first before copying to all cells
Use absolute references ($) correctly:
- $A2 locks column A, row can change
- B$1 locks row 1, column can change
- $A$1 locks both (rarely needed here)
Name your sheets exactly as shown (case-sensitive)
Keep formulas consistent across all cells
Document your customizations in a separate sheet tab

Need More Help?

See GOOGLE_SHEETS_SETUP.md for step-by-step setup
See QUICK_START.md for fastest setup
Check Google Sheets function reference: support.google.com

Custom Setup for Different Requirements

Need 3 people per slot?

Replace all:

/2 with /3
=2 with =3
>2 with >3

Need different colors?

Use these hex codes:

Blue: #4285F4
Purple: #9C27B0
Teal: #009688
Brown: #795548

Need to track multiple time slots per person?

Use CHECKBOXES in form instead of MULTIPLE CHOICE, then adjust formulas to count multiple entries per person.

Last updated: February 2026

Areas & Time Slots Checklist