Sumif using two variables?

Hey there,

I currently have a table for all expenses against projects and a table for all budget line items against projects.

I use these tables are used to filter against a “project dashboard” section that depending on the project selected on a drop down shows all tasks, budget items and expenses.

I want to track expenses against budget line items on the budget line items table so i currently have the formula below for the column written as the following:

SumIf([Project Expenses].TOTAL,[Project Expenses].[Project Title]=[Project Budget].[Project Title] && [Project Expenses].[Budget Line Item]=[Project Budget].[Budget Line Item] )

the formula isn’t giving an error but I’m not getting any values in the columns… So I KNOW i’m doing something wrong haha

Any ideas? Am i using the sumif formula incorrectly?

Hey @Juan_Rey, I’d be curious to see the actual tables and columns you are referring to. The `SUMIF()` formula in Coda can be a little tricky, so I found the best solution is to use the `FILTER()` formula followed by the `SUM()` method at the end. Here is the thread where I discussed my issues with `SUMIF()`.

Hi @Juan_Rey -

Sorry to hear you’ve been having some issues with `SUMIF()`. As @Al_Chen mentioned, it is a little tricky… it can only be used to filter arrays of numbers and sum them (e.g. `LIST(1, 2, 3, 4).SUMIF(CurrentValue > 2) => 7`, whereas in your scenario (I think) you are trying to filter down a set of rows, select a value, and them sum them.

I think this formula might work for you: `[Project Expenses].FILTER([Project Title] = [Project Budget].[Project Title] && [Budget Line Item] = [Project Budget].[Budget Line Item]).TOTAL.SUM()`. With this formula you are filtering rows within the `[Project Expenses]` by those criteria, selecting the `TOTAL` column, and them summing it.

Hope this helps!

Thanks,
alex

Here are the two tables im working with:

@alexdeneui Thanks for stepping in and helping out Alex!

Looks like this is the formula I was looking for thanks for putting it together!

`[Project Expenses].Filter([Budget Line Item] = thisRow && [Project Title] = thisRow.[Project Title]).TOTAL.Sum()`

Need to break free from my excel brain haha. Coda is extremely versatile and powerful cant want to use it full time

2 Likes

help… i try your formula but not working

@Pesan_Paktam : Could you post the formula you used ?

Not sure you need that many `Filter()` to get the `Sum()` of your `Successful` field in the table `[Order - M112019]` and I think you got no field called `total` so the formula can’t find it thus it can’t work.
You’re also comparing a field called `Affiliates` but in your table `[Commission - M112019]` you apparently wish to compare a field called `Affiliates List`.

To get the `Sum()` of `Successful` filtered by `Affiliates List` you just need to compare those 2 `Affiliates List` fields and then get the `Sum()` (If this is effectively the problem)

Could you try this ? (I hope I didn’t make any spelling error writing it)

`[Order - M112019].Filter(Affiliates List=ThisRow.Affiliates List).Successful.Sum()`

Or this maybe :

`Lookup([Order - M112019],Affiliates List,ThisRow.Affiliates List).Successful.Sum()`

i try not working…

It’s because you change the format of the field `Affiliates List` to a `Text` field

Before you were comparing 2 `List` now it’s a `List` and a `Text` which can’t be compare “like this”

I’m not sure where your `Affiliates List` gets the name of the affiliates (I’m assuming it’s in the field `Affiliate ID` and I could be wrong) but could you try this ?

`[Order - M112019].Filter(Affiliates.Affiliate ID=ThisRow.Affiliates ID).Successful.Sum()`

thanks

1 Like