Nested if()

Hello great community.

I'm new in Smartheet and I've a problem and even don't know if a solution exists. I'm trying to create a sort of automatized timesheet for my team.

I would like them to connect to the same sheet. They have to select their name in a list ("Team" column), and the type of mission they've managed in an other list ("Mission" column). But each one doesn't cost the same per day, and the cost is even different depending on the mission.

So, I would like that the cost / day changes automatically ("Cost/day" column) depending on what is selected on "Team" column and "Mission" column.

I hope I'm clear... I tried with nested if() but it doesn't work. If you have a solution it would be really great for me.

Please find below the screenshot and my formula. Thanks a lot for your help. Have a nice day.

=IF(Team1 = "Charlotte"; IF(Mission1 = "Conception"; 400; IF(Mission1 = "Production"; 300; IF(Team1 = "Géraldine"; IF(Mission1 = "Conception"; 1000; IF(Mission1 = "Production"; 800))))))

Find more posts tagged with

Human Resources

Finance

Formulas

Reports

Project Management

Comments

Nic Larsen

This should give you a start on how to build your nested formula, since I don't know how many combinations or conditions you have.

=IF(AND(Team@row = "Charlotte"; Mission@row = "Production"); 800; IF(AND(Team@row = "Charlotte"; Mission@row = "Conception"); 400; IF(AND(Team@row = "Geraldine"; Mission@row = "Production"); 800; IF(AND(Team@row = "Geraldine"; Mission@row = "Conception"); 1000))))

You may be able to shorten this with some OR statements if some combo's are the same.

Ptipok

Hello,

yes it's perfect. Thank you so much for your help and your quick answer

Have a nice day

Ptipok

Dear Nic,

I tried to follow your advice to shorter my fomula, using OR but finally, it doesn't work. For sure, I don't use the right method...

Indeed I have certain mission that cost 0. So I tried this :

=IF(AND(Team@row = "Charlotte"; Mission@row = "Conception"); 800; IF(AND(Team@row = "Charlotte"; Mission@row = "Production"); 500; IF(AND(Team@row = "Charlotte"; Mission@row = "Gestion de projet"); 800; IF(AND(Team@row = "Charlotte"; Mission@row = "Déploiement"); 400; IF(AND(Mission@row = "Mission interne", IF(OR(Mission@row = "Consulting interne", Mission@row = "Avant-vente"); 0;))))))))))

Thanks a lot to help me again. Have a nice day

Nic Larsen

So find it helps at times to split out each of your IF statements to make sure they all line up and then re-stack them.

When I did that on your formula, I found an incomplete IF formula. I also moved your OR statement to the front as the initial check.

= IF(OR(Mission@row = "Consulting interne", Mission@row = "Avant-vente"); 0; IF(AND(Team@row = "Charlotte"; Mission@row = "Conception"); 800; IF(AND(Team@row = "Charlotte"; Mission@row = "Production"); 500; IF(AND(Team@row = "Charlotte"; Mission@row = "Gestion de projet"); 800; IF(AND(Team@row = "Charlotte"; Mission@row = "Déploiement"); 400;

IF(AND(Mission@row = "Mission interne", ???

I think you need to complete that piece, and add it to the end after 400; and then give it another try. Unless I'm not understanding your initial OR statement.

Ptipok

Hello Nic,

It works. Thanks a lot again.

Have a nice day.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…