Calc - SUMIF - between two date condition

classic Classic list List threaded Threaded
3 messages Options
csanyipal csanyipal
Reply | Threaded
Open this post in threaded view
|

Calc - SUMIF - between two date condition

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?



-----
Best Regards from
Pál
--
Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Best Regards from
Pál
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Calc - SUMIF - between two date condition

At 05:07 27/10/2019 -0700, Csányi Pál wrote:

>I am trying to do a calculation in my LO Calc
>spreadsheet. 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 occur out there.
>In the $Incomes.A3 there is the date of the day
>when an income occurs out there.
>
>So what I want is the following.
>I want to sum those prices which occur when the
>good was "food" but only then when that day
>occurred at date stored in the $Incomes.A3 cell or after that date.
>
>What am I doing wrong?

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


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

steveedmonds steveedmonds
Reply | Threaded
Open this post in threaded view
|

Re: Calc - SUMIF - between two date condition

In reply to this post by csanyipal
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)

On 28/10/2019 01:07, csanyipal wrote:

> 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?
>
>
>
> -----
> Best Regards from
> Pál
> --
> Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html
>


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy