How to show conditional sum based on drop down

[Cammie_Mile_Caramelcat123]

Yet again, every time I run into an issue with Coda, I feel like it’s got to have an insanely simple solution, and yet I cannot for the life of me figure it out.

I’ve included a link to the page in question, but to summarise:
I’m trying really hard to add a column that shows a different result/sum based on the Drop Down “Range Type.”

The table is for tracking spells in d&d, and each different range type has a completely different calculation. For Personal and Touch, it should just be 5 by default,
but for Close, it’s 25 + 5 per 2 CL (caster level, but it’s just CL in the table, referred from elsewhere), for Medium, it’s 100 + 10 per CL, and Long is 400 + 40 per CL.

I can get each of these calculations easily enough, as you can see since I’ve made a column for each (out of desperation, lmao, I didn’t think this was necessary initially, but I’m still at a loss), but I want a new column that shows the results of said sums based on the Drop-down Range Type.

Additionally, the reason I can’t just have this calculated elsewhere and referred to is because the CL column is dependant on the Class Drop down- since this is meant to track the spells from all of my classes, not just one, which would have made this much easier. The result should be different depending on the row/entry in question, based on both Class and Range Type.

I am tearing my hair out over this. I feel like the solution must be super obvious, but my ifs and filters aren’t working! I would adore some help here, but please let me know if you need more information- I’m aware the doc is a mess in it’s current form.

[Bill_French]

Hi Cammie, and welcome to the community.

I often run in circles with Coda CFL as well, spending hours sometimes on these seemingly simple challenges. Perhaps this will help, but please note: my recommendation is not from me; it’s from an AI I’m testing using Coda’s new [beta] MCP server. Let me know if this answer is helpful.

@Cammie_Mile_Caramelcat123

add a column that shows a different result/sum based on the Drop Down “Range Type.”

The Challenge

As I understand, you’re attempting to calculate a “conditional sum” for D&D spells where the math changes entirely based on a dropdown selection (e.g., “Range Type”).

• Personal/Touch: Fixed value (5).
• Close: 25 + 5 * (CL / 2)
• Medium: 100 + 10 * CL
• Long: 400 + 40 * CL

Your current approach involves creating multiple helper columns for each calculation and trying to filter or sum them conditionally, which has led to confusion and perhaps and overly complicated table.

The Solution: SwitchIf()

The most efficient “Coda-native” way to handle this is using the SwitchIf() function. This formula evaluates conditions in order and returns the first match, effectively acting as a cleaner, flatter series of “If/Else” statements.

Recommended Formula

This single formula replaces the need for multiple helper columns:

SwitchIf(
  thisRow.[Range Type] = "Personal" OR thisRow.[Range Type] = "Touch", 
  5,
  
  thisRow.[Range Type] = "Close", 
  25 + 5 * Floor(thisRow.CL / 2),
  
  thisRow.[Range Type] = "Medium", 
  100 + 10 * thisRow.CL,
  
  thisRow.[Range Type] = "Long", 
  400 + 40 * thisRow.CL,
  
  0  // Fallback value
)

Demonstration

I have created a dedicated demonstration document to showcase this “Simple Approach” live.

Demo Document: Simple Approach

How it works

In the demo document:

1. Classes Table: Defines the character classes and their levels.
2. Spells Table:
   • Range Type: The dropdown selector.
   • CL: The Caster Level (looked up from the Classes table).
   • Calculation: The single column containing the SwitchIf logic above.

This approach keeps the table clean, the logic centralized, and the performance snappy.