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().
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.
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)
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 ?
“like this”