question

Hey @James Riebau, there are a few ways you could try to implement the COUNTIFS function in FlexSim for your model.

One way would be to write a custom User Command that performs the COUNTIFS functionality; you can then call that in the Global Table.

I wrote a simple one that like this:

/**Custom Code*/
string tableName = param(1);
Variant criteria_row1 = param(2);
Variant criteria1 = param(3);
Variant criteria_row2 = param(4);
Variant criteria2 = param(5);

Table table = Table(tableName);

Array firstCriteria = [];
for (int i = 1; i <= table.numCols; i++) {
  if (table[criteria_row1][i] == criteria1)
  firstCriteria.push(i);
}

int total = 0;
for (int i = 1; i <= firstCriteria.length; i++) {
  if (table[criteria_row2][firstCriteria[i]] == criteria2)
  total++;
}

return total;

It takes in a tableName, two criteria rows, and two criteria (similar to Excel's COUNTIFS function). It will loop over the criteria rows found in the specified table and match the values in the row against the criteria. Call this function in each of the CEmatchCase and CPmatchCase rows and update the colNum (see image above) so it pulls the right values.

---

Another possibility is to transpose your data and use a Table Query. Here's an example:

Table t = Table("CaseErectors1");
int rowNum = 2;
return Table.query("SELECT COUNT(*) FROM (SELECT * FROM CaseErectors1 WHERE Side = $1 AND CaseTyp = $2)", t[rowNum]["Side"], t[rowNum]["CaseTyp"])[1][1];

This query will Select every row in the CaseErectors1 table where the Side is equal to the rowNumber's Side and CaseTyp is equal to the rowNumber's CaseTyp. Then it will count the rows returned. The Table.query() will return a table, so I extract the count from the first cell.

I didn't fill out the CPmatchCase row/column equations, but they are similar to the CEmatchCase row/column. You can perform your equations like normal in the Rate rows/columns using FlexScript.

Here's the model I used to experiment with these things. update_rates.fsm