Formula Issue

Options

I want to get a dollar amount based on if a checkbox is selected. I am running a program where participants can get gift cards as incentives. I am using a separate sheet for the amount for each incentive.

The other sheet columns are named the same as the incentive. Each column is a checkbox. To explain what I want, let me give an example.

John Doe completes incentives 2 and 6. I want the final column to give the total back as $50 based on the checkbox for the incentives selected. I hope this makes sense.

I tried looking around the community to find the right formula. I came across SUMIF on a similar situation. I tried the formula SUMIF({Reference UA Incentives Range 3} [Incentive 1]@row:[Incentive 19]@row).

I thought maybe I was doing it backward, and I tried =SUMIF([Incentive 1]@row:[Incentive 19]@row,1, {Reference UA Incentives Range 3})

I have messed with the formula a few ways to try and see what I'm doing wrong. I get unparseable or an invalid argument error.

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Hi @tworthington

    It sounds like what you need to do is see if a box is checked (is 1 or not) and if so return the corresponding dollar amount from another sheet. SUMIF alone will not do this as it does not know which checkbox column relates to which incentive row. One option is to use a combination of IF and INDEX MATCH. Like this:

    =IF([Incentive 1]@row = 1, INDEX({Incentive Amount}, MATCH("Incentive 1", {Activity Name}, 0)))

    This says if the checkbox in Incentive 1 is checked then return the amount from the Incentive Amount column where there is a match between "Incentive 1" and the value in the Activity Name column. (Incentive Amount and Activity Name being cross sheet references)

    You can then add the same formula for Incentive 2

    =IF([Incentive 1]@row = 1, INDEX({Incentive Amount}, MATCH("Incentive 1", {Activity Name}, 0))) +IF([Incentive 2]@row = 1, INDEX({Incentive Amount}, MATCH("Incentive 2", {Activity Name}, 0)))

    And again for incentive 3, 4, etc.

    =IF([Incentive 1]@row = 1, INDEX({Incentive Amount}, MATCH("Incentive 1", {Activity Name}, 0)))

    +IF([Incentive 2]@row = 1, INDEX({Incentive Amount}, MATCH("Incentive 2", {Activity Name}, 0)))

    +IF([Incentive 3]@row = 1, INDEX({Incentive Amount}, MATCH("Incentive 3", {Activity Name}, 0)))

    +IF([Incentive 4]@row = 1, INDEX({Incentive Amount}, MATCH("Incentive 4", {Activity Name}, 0)))

    +IF([Incentive 19]@row = 1, INDEX({Incentive Amount}, MATCH("Incentive 19", {Activity Name}, 0)))

  • tworthington
    Options

    Okay so there isn't a way to make it a shorter formula?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!