SUMIFS formula in EasyMorph

#1

Hello Team,

Is it possible to replicate the SUMIFS formula in EasyMorph. Below is the formula I am trying to apply but not sure how to do:

=IF(G2=300100,SUMIFS(AP_Subledger_Report_300100!AA:AA,AP_Subledger_Report_300100!A:A,E2,AP_Subledger_Report_300100!G:G,G2))

#2

Replicating SUMIFS logic is possible in EasyMorph but the exact realization depends on whether the criteria ranges are in the same table or not. In your case they are in the same table, so the SUMIFS formula can be replicated with one “Calculate new column” action and one “Aggregate” action.

It’s not clear what the filtering criteria are in your example, so I can’t tell exactly how replicate it in EasyMorph. Below is a sample EasyMorph project that does the same filtering as in the SUMIFS example from the MS Office help: SUMIFS function.

sumifs.morph (4.0 KB)
Book1.xlsx (9.8 KB)

image
image.png784Ă—646 19.2 KB

The calculation logic is as follows:

  1. Produce a column that contains only values that match the filtering criteria.
  2. Aggregate that column

In this example, a new column is calculated using the expression below:

when(startswith([Product], "A") and [Salesperson] = "Tom", [Quantity Sold])

See also the help topic on the when() function: http://help.easymorph.com/doku.php?id=syntax:functions:when

1 Like
#3

Hi @dgudkov,

This works and thanks for the solution!

What would be the solution if the criteria ranges are in other table?

Regards
Vaibhav

#4

The solution would be using actions “Match” and/or “Lookup” to do the matching/lookup with another table first.