SUMIF in Forms on a field

posted on December 14, 2017 • Show version history

Hi,

How would i use the SUMIF fx in Forms from a table?

I have tried both these ways, but not successful. On this field i have set the default value to 0.00 and then it does a subtraction on 2 other fields, if the value return is less than 0.00 or negative then it should stay 0.00, but if the value is greater than 0.00 then it should show the value.

=SUB(INDEX(Table.VariableSub1,ROW()),INDEX(Table.VariableSub2,ROW()))

The above gives me Variable1 for example it can return either a positive or negative value.

=SUMIF(INDEX(Table.Variable1,ROW()), ">0", INDEX([Table.Variable1,ROW())])

=SUMIF(INDEX(Table.Variable1,ROW()), ">0", INDEX(Table.Variable1,ROW()))

=SUMIF(INDEX(Table.Variable1,ROW()), ">0.00", INDEX([Table.Variable1,ROW())])

=SUMIF(INDEX(Table.Variable1,ROW()), ">0.00", INDEX(Table.Variable1,ROW()))

Thanks

Gert

0 0

replied on December 15, 2017

Hi Gert

In this case I don't think SUMIF is the right use for what you want to do. You should be able to accomplish what you want by just using a traditional IF statement

=IF(SUB(INDEX(Table.VariableSub1,ROW()),INDEX(Table.VariableSub2,ROW()))>0,0,SUB(INDEX(Table.VariableSub1,ROW()),INDEX(Table.VariableSub2,ROW()))

The way I have used SUMIF with a table is when I want to calculate items from different rows in the table when a condition on that row is met, which looks like this example below. This calculation is to a field outside the table in this example.

IF the field VRef on the ROW = 1, SUM the Subtotal3 field.

=SUMIF(Requested_Items.VRef,"1",Requested_Items.Item_Subtotal3)

3 0

Discussion

Discussion

Comments 1

Follow-Ups 0

Sign in to reply to this post.