Question

Properly Using the SUMIF Function

Forms Laserfiche

Updated February 9, 2021

asked on February 9, 2021

So I have been working on a table to record the inventory of some bins that we have on site. The thing is, we want to be able to show the grand total, as well as the totals broken up into individual groups depending on who the product belongs to. So a bin could have product from 3 different sources, or just be all one source. I want to see the breakdown in a little summary.

Before they fill out the quantity table, I have them enter the code for the supplier. There is room for up to 3 suppliers. Then I want to be able to have them pick from the entered supplier codes in a drop down menu in the "Grower Code #" column. Then at the bottom, the totals autofill with the correct code in the title, and then the boxes do a SUMIF based on the code.

So my issue is that I cannot get the SUMIF to work, and I also want to be able to add the variables from the entered codes as options in the drop down menu, so we don't have to type in the code 100 times. If anyone has any idea how to do this, the help would be greatly appreciated.

I will attach some screen shots as well.

0 0

Answer

SELECTED ANSWER

replied on February 9, 2021

Oh wait! Removing the quotes and the square brackets was the answer!

Would you also have any idea about getting those g1, g2, g3 variables to populate a drop down in the table?

I really appreciate your help!

1 0

Replies

replied on February 9, 2021

Can you share the variable names for your fields and the calculations you are currently trying to utilize?

0 0

replied on February 9, 2021 • Show version history

Basic SUMIF formula would be something like this:

=SUMIF(table.identifier,"code",table.number)

And a basic SUMIFS formula would be something like this:

=SUMIFS(table.number,table.identifier,"code",table.identifier2,"code2",table.identifier3,"code3")

The calculations mostly follow OpenFormula standard which is commonly used in Excel and Google Sheets - you can find a lot of examples and information online.

0 0

replied on February 9, 2021

So my variables for the 3 codes up top are: g1, g2, g3

The formula I have in the totals box looks like this: =SUMIF(Daily_Receiving_Bin_Inventory__cont__.lot_num, "g1", [Daily_Receiving_Bin_Inventory__cont__.CWT_on_Hand_1])

With lot_num being the column for the condition to be checked, and CWT_on_Hand_1 being the numbers to sum if the condition passes. The box only shows a zero no matter what is typed.

0 0

View 3 previous replies

replied on February 9, 2021

Can you try dropping the square brackets around the variable being summed?

=SUMIF(Daily_Receiving_Bin_Inventory__cont__.lot_num, "g1", Daily_Receiving_Bin_Inventory__cont__.CWT_on_Hand_1)

0 0

replied on February 9, 2021

Dropping the square brackets wasn't the solution. Currently I have:

=SUMIF(Daily_Receiving_Bin_Inventory__cont__.lot_num, "g1", Daily_Receiving_Bin_Inventory__cont__.CWT_on_Hand_1)

Should the variable in quotes be in quotes? Should there be an operator like, "=g1"?

0 0

SELECTED ANSWER

replied on February 9, 2021

Oh wait! Removing the quotes and the square brackets was the answer!

Would you also have any idea about getting those g1, g2, g3 variables to populate a drop down in the table?

I really appreciate your help!

1 0

replied on February 9, 2021 • Show version history

Glad to hear you got it working.

Are you wanting a dropdown that the user selects from?

This is fairly easy, using either:
- The drop-down type field, if you want to limit the options the user can select from.
  -or-
- The Single-Line field with Auto Suggestions, if you want the drop-down to suggest options but allow free-form entry outside of the suggestions.

Or are you saying you want a drop-down field, and to have the selection happen automatically based on something else happening on the form?

This will require Javascript.

0 0

replied on February 9, 2021

I mean like, in the table where I have them enter the code, I would ideally like to have the grower codes entered above be the options in the drop down. If that makes sense.

So if they enter: 20123, 21343, and 45364 in the grower code boxes up top, the drop down menus in each of the table rows has the codes that were entered as the options to pick from.

0 0

replied on February 9, 2021

You could do that with Javascript.

Here's an example.
- This sets to labels/values in the dropdown field when you add a new row to the table - it doesn't edit it on existing rows, just the new row being added.
- You need to update the class names to match what you are using on your form.
  - CSS Class Name of: myTable for the table. It is referenced once each on rows 3, 4, 5, 6, and 7.
  - CSS Class Name of: dropdown1 for the drop-down field within the table. It is referenced once each on rows 4, 5, 6, and 7.
  - CSS Class Name of: field1 for the first code at the top. It is referenced twice on row 5.
  - CSS Class Name of: field2 for the first code at the top. It is referenced twice on row 6.
  - CSS Class Name of: field3 for the first code at the top. It is referenced twice on row 7.

$(document).ready(function () {
  
  $('.myTable .cf-table-add-row').click(function(){
    $('.myTable .dropdown1 select').last().find('option').remove().end();
    $('.myTable .dropdown1 select').last().append('<option value="' + $('.field1 input').val() + '">' + $('.field1 input').val() + '</option>');
    $('.myTable .dropdown1 select').last().append('<option value="' + $('.field2 input').val() + '">' + $('.field2 input').val() + '</option>');
    $('.myTable .dropdown1 select').last().append('<option value="' + $('.field3 input').val() + '">' + $('.field3 input').val() + '</option>');
  });
  
});

0 0

You are not allowed to follow up in this post.

Question

Question

Properly Using the SUMIF Function

Answer

Replies

Sign in to reply to this post.