Hi,
How can I apply a complex SUMIF or COUNTIF? I have several columns say:
DATE
Location
PRICE
STOCK
EAN
I would like to apply something like sumif(DATE > XXX, Location = “Spain” or Location = “Italy”, Price = X) returning the sum of stock after these conditions. This would allow me to apply in one calculated formula all the conditions necessary and not filters and aggregate.
Thank you.
Hi @jmarques,
for conditional aggregation (similar to SUMIF or COUNTIF in Excel) remove unnecessary values first and then aggregate normally using the “Aggregate” action.
Removing unnecessary values can be done in different ways. For instance, use the “Filter by condition” action to keep only rows that satisfy a condition e.g.:
[DATE] > #2019-01-01 and ([Location] = 'Spain' or [Location] = 'Italy') and [Price] = 10
Alternatively, you can calculate a new column where unnecessary values are removed (replaced with empty values) and the “good” values are kept. It can be done using the “Calculate new column” action with an expression, e.g.:
if([DATE] > #2019-01-01 and ([Location] = 'Spain' or [Location] = 'Italy') and [Price] = 10, [Stock], empty())
or its shorthand version using when() and in():
when([DATE] > #2019-01-01 and in([Location], 'Spain,Italy', ',') and [Price] = 10, [Stock])
Calculating a new column with unnecessary values removed is convenient when multiple conditional aggregations with different conditions have to be calculated on the same dataset. In this case a new column is calculated for each condition aggregation.
For more complex cases with nested IF/THEN/ELSE condition, it may make sense to use the “Rule” action is it’s better suited for such cases.
See below example shows how to perform condition aggregation in various ways.
sumif.morph (5.4 KB)
