Calc - SUMIF - between two date condition

users
#1

Hi,

I am trying to do a calculation in my LO Calc spredsheet.
The function that I want to run is the following:

SUMIFS($Expenses.I2:I323;$Expenses.K2:K323;"food";$Expenses.B2:B323;">="
$Incomes.A3)

but get the Error:509 in the Result cell.

In the I2:I323 range there are prices of goods out there.
In the K2:K323 range there are names (types) of those goods out there.
In the B2:B323 range there are dates of days when expenses occures out
there.
In the $Incomes.A3 there is the date of the day when an income occures out
there.

So what I want is the following.
I want to sum those prices which are occured when the good was "food" but
only then when that day occured at date stored in the $Incomes.A3 cell or
after that date.

What am I doing rong?

#2

Your first criterion, "food", assumes the "=" operator by default: that's fine. But in your second criterion, for dates, you have needed to specify the ">=" operator. Your problem is then that you have simply positioned this text string next to the cell reference "$Incomes.A3", no doubt hoping that they will somehow be combined. But you need to concatenate the two parts explicitly - using the "&" operator. So your expression should end
...;">="&$Incomes.A3)

Err:509 means "Missing operator", so that makes sense.

PS: The answer to your second query is very similar.

I trust this helps.

Brian Barker

#3

From the help page examples (which I have to say are excellent), try

SUMIFS($Expenses.I2:I323;$Expenses.K2:K323;"=food";$Expenses.B2:B323;">="&$Incomes.A3)