Trouble using SUMIF method in Calculations

posted on November 14, 2023 • Show version history

I want to sum a column of an Expenses table where the description matches the description on the row of my Rates table.

After reading the method description I had to make many inferences.

Filters the list for values that satisfy the condition and then calculates the sum of those values. If no sum range is specified, all values in the range will be summed.

=SUMIF( table_variable_name.column_variable_name, ">5", [table_variable_name.column2_variable_name])

It was not clear if the quotes are always needed around the condition or only if using static values.

When it says a sum range, it makes me think of a max row count but the example shows just a variable in there, which then makes me think that is where I put the field I want to sum. Yet the language doesn't add up (no pun).

So here is what I wrote, which does not throw any errors, but the value is always zero.

=SUMIF(Expenses.Description,"=INDEX(Rates.Description,ROW())",[Expenses.Amount])

0 0

replied on November 14, 2023

The quotes are if you are doing an evaluation other than equality, such as > or <. If you are just comparing equality to a single value, you don't need to indicate =, so you shouldn't need the quotes.

I didn't throw together a test on this, just eyeballing it here, but maybe try this:

=SUMIF(Expenses.Description,INDEX(Rates.Description,ROW()),Expenses.Amount)

I'm not quite certain how it's going to work across your two tables, so it still might not work.

0 0

View 4 previous replies

replied on November 14, 2023

Just tried it this way without any luck, still zero for all values with no error. I think the final parameter is the problem here. It is not clear where I put what field I want to sum in this method. I had to use the first 2 params just to get the condition set. The method appears to be written like If fieldX = value sum fieldY

Therefore (fieldX,value,fieldY)

0 0

replied on November 14, 2023

Where exactly do you have this function? Is it in the Rates table, the Expenses table, or as a standalone field outside of any tables/collections?

Per the documentation,

If you only provide 2 parameters, it sums values from the 1st parameter/list that match the condition.

If you provide 3 parameters, it sums values from the 3rd parameter/list where the column specified in the 1st parameter matches the condition.

However, if you're trying to work across two different tables a lot more context is required to know what is needed/possible.

0 0

replied on November 14, 2023

The function is in the Rates table set on the Taken field.

=SUMIF(Expenses.Description,INDEX(Rates.Description,ROW()),[Expenses.Amount])

From your description, it seems I should get a value of 100 on the 3rd row, but it is always zero.

0 0

replied on November 14, 2023 • Show version history

Remove the brackets from around Expenses.Amount

Brackets indicate a list of values like [1,2,3] so you shouldn't use them when the referenced variable is already a list/collection.

Since the table column is already a list, the brackets are effectively creating a nested list with that column/list as the only value like [[1,2,3]]

The formula Matthew provided works as-is

=SUMIF(Expenses.Description,INDEX(Rates.Description,ROW()),Expenses.Amount)

2 0

replied on November 14, 2023

That was it! I wonder why my instructions appear different than yours. When I visit the page I see this which specifies you must use the brackets even if referencing a single table variable.

https://doc.laserfiche.com/laserfiche.documentation/en-us/Default.htm#../Subsystems/ProcessAutomation/Content/Forms-Current/Formulas.htm#Function

0 0

replied on November 14, 2023

Looks like the one you're viewing is the Cloud documentation and the one I am looking at is the version 11 documentation.

Not sure if Cloud is meant to be that way or if that's just a typo.

0 0

replied on February 28, 2024

Cloud documentation was updated to match documentation for self-hosted forms. The brackets were indeed a typo.

1 0

Discussion

Discussion

Trouble using SUMIF method in Calculations

Comments 9

Follow-Ups 0

Sign in to reply to this post.