Count/IFSum formula Question

Options

Hello;

for a financial sheet I am trying to find a formula combination.

the scenario goes as follows: if person A ticks a box in column X, a certain amount of due paiment (column Y) will be reduced by 30k (up until 0). The total sum (column Z) that person A will need to pay is the sum of all relevant columns, including the due paiment in column Y (if it's 0, it's 0 euros).

Additionally, the amount that shows in column Y (that will be reduced by 30k if a box is ticked) is a certain percentage of the sum that is shown in column W.

How can I make it so that I only need to fill in the amount in column W, and potentially tick the box in column X, to resolve this calculation? I have tried a few solutions but it'll only make me do as much work as I'd calculate it myself.

• ✭✭✭✭✭✭
Options

Are you able to provide some screenshots (sample data if needed) for context?

• ✭✭✭✭✭✭
Options

For the first part:

=IF(X@row = 1, MAX(Y@row - 30000, 0), Y@row)

If the checkbox in X is ticked, then 30000 will be subtracted from Y, or 0 if this would be negative.

It's a bit tricky to work out what the next formula regarding column Z would be as the columns involved haven't been specified, but you should be able to do this easily with a SUM formula.

For the percentages, if you're wanting to check that your percentage is acceptable pre/post adjustment then you can have a couple of columns to show this. For example, if you want what percentage Y is of W pre/post modification:

Pre modification:

=Y@row / W@row

Post modification:

=MAX((Y@row - 30000) / W@row, 0)

This assumes you want 0 as a minimum, otherwise you can ditch the MAX portion to include negatives:

=(Y@row - 30000) / W@row

You can then use a filter in needs be to display acceptable/unacceptable results and/or combine with conditional formatting.

Hope this makes sense, but if I've misunderstood something or you've any problems/questions, then just post! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!