Formula Help - Calculating Value based on Results within Two Status Columns

I am trying to calculate different values based on the results in two status columns.

For example this use case is tracking costs for contractor graphic designers.

Status Column - Graphic Assignment
John
Stacy
Ryan

Package
Beginner
Basic
Pro

The ad design cost varies based on the package price as well as which designer is chosen.

For example: If package= Beginner and Graphics Assignment is John $50
If package= Pro and Graphics Assignment is Stacy $90

Any suggestions on a formula?

@membermarketing

Welcome to the Monday Community!

Yes this can be achieved. You will need to employ the Formula Column with two Switch Formulas:

Function: SWITCH
Description: Checks if a condition on a specific value is met, if so, returns the result of that value, o/w returns the default result (if exists). The pattern is: SWITCH({Column}, “val1”, “result1”, [“val2”, “result2”], …, [“default”])
Example: SWITCH({Priority}, “High”, 3, “Medium”, 2, “Low”, 1, 0) => 2 (in case priority is “Medium”)

So in your case, the formula would look something like this:

(SWITCH({GraphicAssignment},“John”,x,“Stacy”,y,“Ryan”,z)+SWITCH({Package},“Beginner”,x,“Basic”,y,“Pro”,z))

Replace the x,y,z with your $ values associated with each status. Note that the SWITCH formula is case sensitive.

Hope this helps!

1 Like