CODEWITHSUNDEEP
excelexcel-formulasumifscomparison-operatorsexcel-tables
user6828404
user6828404

Reputation: 1

How to use comparison operators within a SumIfs formula using structured table references

I am trying to use the SUMIFS function to sum all entries in a table that fall between the date ranges specified in another table. I cannot get the comparison operators (">=" or "<") to work. Every variation I make results in an incorrect sum of 0. I have scoured the internet but haven't found an answer.

Here are a couple variations I have tried:

Variation 1: 

=SUMIFS(tblHoldings[Amount],tblHoldings[Dates],">=[@Dates]",tblHoldings[Dates],"<CurrentHoldings!C4",tblHoldings[Investment],tblCurrentHoldings[[#Headers],[LargeStock]])

Variation 2:

=SUMIFS(tblHoldings[Amount],tblHoldings[Dates],">="&[@Dates],tblHoldings[Dates],"<"&CurrentHoldings!C4,tblHoldings[Investment],tblCurrentHoldings[[#Headers],[LargeStock]])

Other variations have resulted in generic "There-is-a-problem-with-this-formula" error messages. If I remove the comparison operators, the formula will return a sum, so I know the references are correct. I just need to filter those references by date. When I evaluate the Variation 2 formula, the ">="&tblHoldings[Dates] and "<"&tblHoldings[Dates] references evaluate to a #Value error. This seems like it should be a simple formula. What am I overlooking?

Thanks!

Here is a screenshot of the table with the formula (formula returns 0): screenshot

Here is a screenshot of the table on the same worksheet as the range table (formula returns correct answer): screenshot

Here is the file itself: https://1drv.ms/x/s!ArArDJ7WmD62grkh5Crfi0m3k_m8GQ?e=yb888R

Upvotes: 0

Views: 365

Answers (1)

Jenn
Jenn

Reputation: 647

It sounds like your references may be getting mixed up if it works on one worksheet and not the other. Here is an example of a formula that works perfectly as SUMIFS with multiple tables on multiple sheets. In this example, arguments are references to structured tables with the exception of the last, which is a static text string.

=SUMIFS(t_Claims[f_PaidClaims],t_Claims[f_Month],[@Month],t_Claims[Data Category],"Medical"))

Here is another example, but in this scenario, I've swapped out the last critera for a cell value on another worksheet. Note the single quotation marks around the worksheet name. 'Sheet Name' I don't see those in your formula. You can also see that by adding a reference to another worksheet, the references to other columns in the same table (@[Month]) have been fully qualified by the table names being added as well t_DataSummary_ByMonth[@Month].

=SUMIFS(t_DataSummary_ByMonth[Fixed Costs],t_DataSummary_ByMonth[Month],">="&t_DataSummary_ByMonth[@Month],t_DataSummary_ByMonth[Month],"<"&'Fees - Update Annually'!E269)

I suggest highlighting this portion of your formula and remapping it: 

CurrentHoldings!C4

If it should be an absolute cell reference, add the dollar signs:

'CurrentHoldings'!$C$4

If you are referencing another cell value on another table, such as above, also fully qualify your column references that are in the same table as the formula, especially if the column headers are the same in both tables.

Upvotes: 0

Related Questions