SUMIF, INDEX, MATCH and Calculate percentage formula

Options
✭✭

If a column has a specific value "A&D Fee", I want to go find a column in another sheet "Design Fee", MATCH the Master RE ID numbers and then find 5% of that value. This is the formula I've tried but it does not work. Can anyone offer some suggestions? Thank you.

=SUMIF(Type@row = "A&D Fee", (INDEX({Design Fee}, MATCH([Master RE ID]@row, {TOK RE ID} * 0.05, 0))))))

• ✭✭✭✭✭✭
Options

I normally write that expression as:

=IF(Type@row = "A&D Fee", (SUMIF({Master RE ID}, [Master RE ID]@row, {TOK RE ID}) * 0.05), 0)

Where:

• {Master RE ID} is that column from the Design Sheet
• [Master RE ID]@row points to the row within the sheet that you are doing the calculation
• {TOK RE ID} is the column in the Design Fee sheet where you are storing the number that you need the 5% calculation performed.

If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!