Can't figure out this multiple IF formula

Platform discussions
, ,
1

Having issues figuring out this formula. Each Item Name has its own line item. When a new amount (number column) is added for Scheduled Units, I need the Formula column to multiple that input by the following…

Example: If there are 4 Pecos units, total is 8.

IF({Name}=“Pecos”,{Scheduled Units}*2,
IF({Name}=“Ora 3/4”,{Scheduled Units}*3,
IF({Name}=“Ora 1/4”,{Scheduled Units}*1,
IF({Name}=“Sierra Ranch Uppers”,{Scheduled Units}*1.5,
IF({Name}=“Sierra Ranch Lowers”,{Scheduled Units}*1.25,
IF({Name}=“Pointe South Mountain Uppers”,{Scheduled Units}*1,
IF({Name}=“Pointe South Mountain Lowers”,{Scheduled Units}*2,
IF({Name}=“Cortland South Mountain Khaki”,{Scheduled Units}*2,
IF({Name}=“Cortland South Mountain Hardrock”,{Scheduled Units}*11,
IF({Name}=“Cortland South Mountain Sweet Maple”,{Scheduled Units}*5,
{Scheduled Units}
)))))))))))

2

Hey Ryan!

Can you please share a screenshot of your board so we can replicate this on our end and test the formula? Thank you :pray:

3

This is probably a simpler route…

={Scheduled Units}*SWITCH({Name},“Pecos”,2,“Ora 3/4”,3,“Ora 1/4”,1,“Sierra Ranch Uppers”,1.5,“Sierra Ranch Lowers”,1.25,“Pointe South Mountain Uppers”,1,"Pointe South Mountain Lowers”,2,“Cortland South Mountain Khaki”,2,“Cortland South Mountain Hardrock”,11,“Cortland South Mountain Sweet Maple”,5,1)

The last “1” in the is the “default” value it will multiple by if no matching name is found.

2 Likes
4

Screenshot 2023-08-24 at 9.13.11 AM
Screenshot 2023-08-24 at 9.13.11 AM1373×621 49 KB

5

Hi Ryan

You should be able to use the following formula… rather than using the name it gets the number directly from the Sheets Needed column by taking the number from within the brackets…

{SCHEDULED UNITS}*LEFT(RIGHT({SHEETS NEEDED PER UNIT},LEN({SHEETS NEEDED PER UNIT})-1),SEARCH(“)”,{SHEETS NEEDED PER UNIT})-2)