# Sum all items from the given year

R

#### Ricardo

I have two columns:
A - formated as Date ("YYYY-MM-DD") with dates of purchase
B - formated as Number with cost of purchase
The columns constantly grow or shrink as I am adding new items or deleting them.
I already figured out how to sum up the global cost of purchase without having to define a rigid range in the B column:
=SUM(\$B:\$B)
It works just fine for me summing up all of the non-empty cells in column B.
Now I would like to sum up the cost of purchase for a given year, eg. 2011.
It should probably look like:
=SUM.IF(\$A:\$A;"GIVEN YEAR";\$B:\$B)
However, I cannot figure out how to define the condition for "GIVEN YEAR"

I

#### isabelle

hi Ricardo,

=SUMPRODUCT((YEAR(A2:A100)=2011)*(B2:B100))

R

#### Ricardo

Thanks a lot Isabelle. It worked like a charm!
Would you please tell me how to count all purcheses for a given year?

I

#### isabelle

ok but i'm not sure if is that what you want

=SOMMEPROD((ANNEE(A3:A65536)=2011)*1)

=SOMMEPROD((ANNEE(A3:A65536)=2011)*(B3:B65536))

C

#### Cimjet

Isabelle
Les formules sont en francais.
Cimjet

I

#### isabelle

oups sorry,

=SUMPRODUCT((YEAR(A3:A65536)=2011)*1)

=SUMPRODUCT((YEAR(A3:A65536)=2011)*(B3:B65536))

R

#### Ricardo

Great! Thanks again Isabelle!!! You just made my day!!!
Best wishes,
Ricardo