Formula Issue

tworthington

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.

KPH

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

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

tworthington

@KPH

"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."

I was leaving out details, but is there an easier way to make this work where the sheet can match Incentive 1 on the reference sheet to the incentive 1 column?

I could also make a row at the top with the incentive name to match the reference sheet that can be hidden.

KPH

If you replaced the checkboxes with 1s and 0s you could do a simple multiplication and would not need the IF. You would just be multiplying the 1 or 0 by the incentive amount.

If you can ensure that your incentives are always in the correct order and you aren't going to need to add more rows between rows, you could use a simple INDEX to find the right one based on the row number rather than MATCH. This isn't as robust as INDEX MATCH but it is a shorter formula.

So your first incentive would be

=(INDEX({Incentive Amount}, 1, 1) * [Incentive 1]@row)

This brings the value from row 1 column 1 and multiplies it by the value in [Incentive 1]@row

The second would be

=(INDEX({Incentive Amount}, 2, 1) * [Incentive 2]@row)

This brings the value from row 2 column 1 and multiplies it by the value in [Incentive 2]@row

And you'd join those together to find the total of both incentives

=(INDEX({Incentive Amount}, 1, 1) * [Incentive 1]@row) + (INDEX({Incentive Amount}, 2, 1) * [Incentive 2]@row)

And continue like that for the other incentives.

=(INDEX({Incentive Amount}, 1, 1) * [Incentive 1]@row)

+ (INDEX({Incentive Amount}, 2, 1) * [Incentive 2]@row)

+(INDEX({Incentive Amount}, 3, 1) * [Incentive 3]@row)

+ (INDEX({Incentive Amount}, 4, 1) * [Incentive 4]@row)