Question

Forms IF Formula to suppress results before giving value of calculation -- what's wrong?

Forms

Updated December 19, 2022

asked on December 15, 2022

I am using a currency field outside a table to calculate data within a table. I have tried with and without the INDEX/ROW language, neither work. Want to suppress the value from the calculation if the first referenced field is blank. P.S. The SUM formula on it's own, works as listed in the first example below.

Without INDEX/ROW:

=IF(Supplemental_Calculation.V_1__Actual_2022_Gross_Receipts)<>"",SUM(Supplemental_Calculation.V_3_Amount_Due),"")

With INDEX/ROW

=IF(INDEX(Supplemental_Calculation.V_1__Actual_2022_Gross_Receipts,ROW())<>"",SUM(INDEX(Supplemental_Calculation.V_3_Amount_Due,ROW()),"")

0 0

Replies

replied on December 15, 2022

First, be careful with the parentheses count/placement, in your first example you have a closing parentheses after the column variable, which would break the entire calculation formula.

Next, the ROW() function you're using only works when the calculation is within the table because that value is basically a reference to the index of the "current" row in the table. You should only use ROW() for INDEX functions that are inside of a table/collection.

If you're only trying to check if the first row is empty, you should replace ROW() with 1, because INDEX(Table.Column,1) will tell the formula to get the value of the first row.

Additionally, you do not want the INDEX function in the sum because INDEX retrieves a specified row of the table, but for the sum you want every value.

=IF(INDEX(Supplemental_Calculation.V_1__Actual_2022_Gross_Receipts,1)<>"",SUM(Supplemental_Calculation.V_3_Amount_Due),"")

Another option would be to check if any of the rows are empty, which you could do with COUNTIF

For example =COUNTIF(Table.Column,"=") checks for empty fields, meaning you could make the IF statement check if the resulting value is less than 1,

=IF(COUNTIF(Supplemental_Calculation.V_1__Actual_2022_Gross_Receipts,"=")<1,SUM(Supplemental_Calculation.V_3_Amount_Due),"")

0 0

replied on December 15, 2022

Thank you for your reply!

No luck. The goal is in line with the last solution you provide. There can be between 1 and 3 rows in this particular table, with a default of 1 unless the user adds more, or more rows display based on data present in a table lookup. I want for the field in question to give me the sum of the amounts in the column: Supplemental_Calculation.V_3_Amount_Due as long as there is a value entered in Supplemental_Calculation.V_1__Actual_2022_Gross_Receipts on the same row.

The more I think about this, the more I think I will need to account for the presence of a value in the table itself (total of each row) and the other field will take care of itself. The IF formula I'm using here with a subtract function is not working either, however:

=IF(INDEX(Supplemental_Calculation.V_1__Actual_2022_Gross_Receipts,ROW())<>"",SUB(INDEX(Supplemental_Calculation.True_Tax_Amount_1,ROW()),INDEX(Supplemental_Calculation.V_5__Tax_Paid_in_2022,ROW()),"")

0 0

replied on December 19, 2022

Double check your parentheses. You should see a syntax error in the designer because you seem to be missing a closing parentheses on the SUB function.

I count 8 Opening Parentheses, but only 7 closing, so you'll need to correct the syntax before you can tell if the formula will actually work.

0 0

You are not allowed to follow up in this post.

Question

Question

Forms IF Formula to suppress results before giving value of calculation -- what's wrong?

Replies

Sign in to reply to this post.