SUMIFS and advanced formulas

[K307]

Hi, guys!

Problem:
I need vanilla SUMIFS from Google Sheets. I need to sum "expenses’ that have certain text attribute and a boolean flag.

Tried solutions:

1. Rollup with lookup - can’t Rollup on a Lookup column
2. If-Else-Then’s - can’t have multiple conditions in ITE column.
3. Hyperofurmulas - they have been removed from Glide.
4. Formulas in Google sheet itself - looks like Glide cannot parse columns with Google formulas.

Is there a solutions to this? Thanks in advance!

[Jeff_Hager]

IF columns can have multiple conditions if you change your thinking a bit and try to think backwards by eliminating all the false conditions first. The IF column structure is limiting, but a lot of times you can work around it with some creative thinking.

As for rollups, you can also consider creating template columns that join several columns together, then use that template to create a relation, then point a rollup column to that relation instead of the entire table.

@K307

Rollup with lookup - can’t Rollup on a Lookup column

This may be because you are trying to do a rollup against an array. If your Lookup is against an entire table, then it is definitely an array. If your lookup is against a multiple relation, then it’s still an array. If your lookup is against a single relation, then it should be a single value that you can then use with a rollup.

If you share a screenshot of your data and what you are trying to accomplish, I’m sure we can come up with a working solution.

[K307]

@Jeff_Hager Thanks for the elaborate answer! I’ve looked up all the methods you mentioned, but don’t see any promising options yet.

I’ll show my example to make it more descriptive:

Here is my table. I need to:

SUM all Amounts that have “User A” AND have is Deleted == false

How do I go about this?

[Jeff_Hager]

There’s a few ways to do it, but I would probably suggest this:

• Create a template column that joins your ‘Paid For’ and ‘is Deleted’ columns.
• Create a multiple Relation column that links that template column back to itself.
• Create a Rollup column that sums the Amount from the relation.
• If you want, you can then add an IF column that only returns that rollup value if ‘is Deleted’ is not true and it matches the user. This is the backwards thinking I was referring to.

IF 'Paid For' is not User A Then 0
ElseIF 'is Deleted' is true Then 0
Else Rollup Amount

Another variation would be:

• Create a multiple relation that links the ‘Paid For’ column back to itself.
• Then create an IF column that returns the amount or zero based on the is deleted value. If true then zero…else amount.
• Then use a rollup column against the relation that sums the IF column amount.

Some advice when you use booleans, it’s better to check for true or not true, to also account for times that the column value could be empty. Checking for false can get you into trouble if you have the potential for a boolean column to be empty.

[gvalero]

Hola,

there is a plan C if you want to go beyond to Jeff’s suggestion: JavaScript code

I know it’s not a “no code solution” but sometimes, not all can be “no code”

Here my example: Imagine you want to have all values with Date=“2021-Q1” and “Expired=True”, your table could look like this

1. your JS code might be this, something short and able to save columns
   

   

2. Later, we need to convert this result (text) to a numeric value using a Math column and get the Total (sum) by using a Rollup column.
   

   

But let’s complicate the case!
Imagine now we want to have the Total (Sum) associated to all Q1s no matter the year (2019/2020/2021-Q1) with “Expired=True”, so the JS code would be:

if ((p1=="2019-Q1" || p1=="2020-Q1" ||  p1=="2021-Q1") && p2==true)
   return p3

and after converting the above result to a numeric value and use a rollup to sum values, your table will look like this:

I hope it helps.

Saludos!

[ThinhDinh]

Well done! I’m in favour of adding a bit of JS nowadays, it reduces the use of multiple columns for me.