# 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

Hi there!

As mentioned in @andrewalmandâ€™s response, the SWITCH formula is a wonderful option to output a value based on two conditions!

I would propose one change to the already amazing formula that was recommended: adding in a default option after the SWITCH functionâ€™s values and results (in the below formula, this can be indicated by â€ś0â€ť:

SWITCH({Graphic Assignment}, â€śJohnâ€ť, 3, â€śStacyâ€ť, 2, â€śRyanâ€ť, 1, 0) + SWITCH({Package}, â€śBeginnerâ€ť, 3, â€śProâ€ť, 2, â€śBasicâ€ť, 1, 0)

The change here is adding in the default value of 0 at the end of each SWITCH function. Hope this makes sense

Hi @JCorrell / Jim,

Thank you so much for reaching out about this! This was my first post in the community as I am very new to the team. I truly appreciate all of your feedback and have since edited my post to ensure I am not overlooking the amazing work of Andrew!

In my post, I was trying to add more insight into the formula since it appeared that the proposed formula was missing the default values required in a SWITCH formula, which if missing, will result in the formula to error. My sincere apologies for how that came across, and the solution has since been listed to the original post instead.

Again, thank you so much for reaching out, youâ€™ve been extremely helpful as I navigate communicating with the community

1 Like