Formula - IF/OR


I am attempting to take the SUM of one cell and divide it by a range of other cells to get a percentage output.

However, when creating the formula I am receiving an "#Incorrect Argument" error. I need the range in the column(s) because the column "Region Name" will fluctuate with the entries that are made from the source sheet to my metrics sheet (screenshots). So I am attempting to get a utilization percentage of each "Deal Type" per "Theater"

=IFERROR(SUM(Value@row / Value60:Value95, 0))

I have also attempted to do the formula this way as well, I think I am missing a variable but not 100%. This formula does not give an error but I am not getting the percentage from the total of all the AMER's which according to my calculations would be 27% under Theater AMER (row) in the Consulting Cell.

=IFERROR(SUM(Value61:Value96, "AMER", "Consulting" / Value@row), 0)

I would need it to look like this when all said and done

I am ultimately trying to create a dashboard so I think the information needs to be side by side maybe I need to create a different metric(s) sheet? Maybe re-think the entire structure for the percentages....?

So that will have the running totals of each theater divided by each deal type.

Any guidance or assistance is greatly appreciated

Thank you!



Best Answer

  • ✭✭✭✭✭
    Answer ✓

    Don't you just want

    =Value@row / SUM(Value60:Value95) ?


    =IFERROR(Value@row / SUM(Value60:Value95), 0)

    If you are then dragging the formula

    =IFERROR(Value@row / SUM(Value$60:Value$95), 0)

    will keep your range from moving as you drag

    Let me know how that goes


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!