Formula to show results based on two status columns combination

Hi, I am trying to come up with a formula to do the following:

  • I have two status columns, and a numbers column. The first stats column tells me if a client is being billed directly or through us. The second one to tell me if there is a markup, and the numbers column has the invoiced amount.
  • I am able to use the IF statement to either calculate and show the final number with or without markup if I change the second status column to yes or no. HOWEVER, I would only like it to show a result IF I change the first status column to “billed through us.” If that is not the case, I don’t want it to show anything. Is there a way to do this with a formula?

Thank you in advance.

Hi @alonsovst, Alonso

Did you tried something like this:

IF({Stat1}=“Through us”,IF({Stat2}=“Yes”,{Number}*3,{Number}),"")

So, if billed through us display an amount (uplifted or not depending on the second status), else display nothing.

Thanks for the quick response, Bas. I tried using the formula but it returns as illegal. Here’s a screenshot to visualize how I am hoping it works.

  • If I select Via Us in the Billing column + No in the markup column, it will copy the value that’s in the invoiced amount column over to the billback to client column. And if I select Via us + Yes, it will add the markup to the invoiced amount in the billback column. However, if I select anything other than “Via Us” it should not show anything in the billback column.

Screen Shot 2020-03-09 at 5.47.56 PM

Hi Alonso,

This ons should work then:

IF({Billing}=“Via Us”,IF({Markup}=“Yes”,{Invoiced Amount}*1.15,{Invoiced Amount}),"")

If Billing is anything else then “Via Us” it will return empty string. If Billing is set to “Via Us” it will return the Invoiced Amount (in case Markup is not “Yes”) or a 15% markup on Invoiced Amount (in case Markup is “Yes”).

In case you need to handle the “N/A” situation differently the “No” you could look to SELECT in stead of second IF.

Thanks for the input again! For some reason it still returned the illegal formula message, but I was able to modify it to this and it is almost working: IF(AND({Billing}=“Via Us”,{Markup} =“No”),{Invoiced Amount},IF(AND({Billing}=“Via Us”=“Yes”,{Markup} =“Yes”),{Invoiced Amount}*1.15,""))

The only thing it doesn’t seem to be doing is replacing the value “true” with the calculation. It doesn’t show anything when I select markup = yes.

Any thoughts?

I think I understand what you are trying to achieve. In your formula:

IF(AND({Billing}=“Via Us”,{Markup} =“No”),{Invoiced Amount},IF(AND({Billing}=“Via Us” :grinning:= “Yes”:grinning:,{Markup} =“Yes”),{Invoiced Amount}*1.15,""))

The part between the smiley faces looks not ok to me, is that a typo mistake?

The formula I supplied is doing exactly what you want on my test board. Depending on your timezone we can do a Zoom session to go through it. I am living in CET timezone. I can also give you guest access to the board I used for testing this one, just let me know your email.

That part of the formula was added on purpose. And that would be great, my email is avsemsignups@gmail.com.

Thank you!

Thanks for sharing the test board with me @basdebruin I realized that the error was happening because the name of my “Invoiced” column has two words. I changed it to once and it is working fine now.

1 Like