sum all of a particular year in date column

Asked by R. A. Montgomery on 2012-12-22

How do I pull all records of a particular year from a multi year database?
i.e. using a sumif statement: SUMIF(database.C2:C1000, "Year=2010" , database.B2:B1000) where C2:C1000 is a multi-year Date column. What do I put in for "Year=2010"?
Thanks
Monty

Question information

Language:
English Edit question
Status:
Answered
For:
Ubuntu openoffice.org Edit question
Assignee:
No assignee Edit question
Last query:
2012-12-23
Last reply:
2012-12-23
Tony Pursell (ajpursell) said : #1

Hi

Can you be a bit more specific. Which module is the database in, Calc (a spreadsheet) or Base (a database)?

If it is a database in Calc, then there are specific database functions. See

https://help.libreoffice.org/Calc/Database_Functions

for how they work.

But for a simple SUMIF, where C2:C1000 has year values like '2010' and B2:B1000 has quantities, then

SUMIF(C2:C1000,"2010",B2:B1000)

will add up all the quantities in B2:B1000 in rows where C2:C1000 contains '2010'. See

https://help.libreoffice.org/Calc/Mathematical_Functions#SUMIF

Hi Tony,
It is a Calc database and the field is a full date field month/day/year.
I have looked through all the database functions and did not see any
that popped out amt me.
I need a way to mask the month/day out of the field.
Does that make any more sense?
Monty
On 12/22/2012 03:35 PM, Tony Pursell wrote:
> Your question #217436 on openoffice.org in Ubuntu changed:
> https://answers.launchpad.net/ubuntu/+source/openoffice.org/+question/217436
>
> Status: Open => Answered
>
> Tony Pursell proposed the following answer:
> Hi
>
> Can you be a bit more specific. Which module is the database in, Calc
> (a spreadsheet) or Base (a database)?
>
> If it is a database in Calc, then there are specific database functions.
> See
>
> https://help.libreoffice.org/Calc/Database_Functions
>
> for how they work.
>
> But for a simple SUMIF, where C2:C1000 has year values like '2010' and
> B2:B1000 has quantities, then
>
> SUMIF(C2:C1000,"2010",B2:B1000)
>
> will add up all the quantities in B2:B1000 in rows where C2:C1000
> contains '2010'. See
>
> https://help.libreoffice.org/Calc/Mathematical_Functions#SUMIF
>

Tony Pursell (ajpursell) said : #3

I suggest you make a new column with the YEAR() function in each cell so you can use that instead of the column with the full date in. If necessary, you can hide this column.

Can you help with this problem?

Provide an answer of your own, or ask R. A. Montgomery for more information if necessary.

To post a message you must log in.