CODEWITHSUNDEEP
excel
jamiet
jamiet

Reputation: 12294

SUMIFS less than criteria in a table

I have a simple table in a workbook to track monthly spend per item. What I'd like to do is work out the cumulative spend per item - I know I can do that using SUMIFS() but I can't figure out the correct syntax.

Here's a screenshot of the workbook currently: enter image description here

The formula for CumulativeSpend is

=SUMIFS([Spend],[Item],[@Item], [YearMonth], [@YearMonth])

however all that does is provide the same number as [@Spend]. What I need to do is sum up everything from [@Spend] where [YearMonth] is less than or equal to `[@YearMonth] but I can't figure out the syntax. I have tried all of the following:

=SUMIFS([Spend],[Item],[@Item], [YearMonth], <=[@YearMonth])
=SUMIFS([Spend],[Item],[@Item], [YearMonth], "<="[@YearMonth])

all of which are invalid formulae. I've also tried this:

=SUMIFS([Spend],[Item],[@Item], [YearMonth], "<=[@YearMonth]")

which is a valid formula but (unsurprisingly) it doesn't give the right answer.

A read-only copy of the workbook I've used here can be accessed at: https://1drv.ms/x/s!AjcmU60daA9VquZIhFGJlUyysNmFCw

Can anyone give me the formula to calculate the CumulativeSpend per item per month?

Upvotes: 1

Views: 588

Answers (2)

jamiet
jamiet

Reputation: 12294

Ah, immediately after posting this I figured it out!

=SUMIFS([Spend],[Item],[@Item], [YearMonth], "<="&[@YearMonth])

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152535

You need to concatenate the string with &:

=SUMIFS([Spend],[Item],[@Item], [YearMonth], "<=" & [@YearMonth])

Upvotes: 2

Related Questions