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" :(
Please help!
 
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?
Thanks a bunch in advance!!!
 
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))
 
I

isabelle

oups sorry,

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need some PWA help 0
The lowest of all? 6
Word 2016 opening sooo slow 0
Start a mail merge upon opening the document 1
Automation of scanned file storage based on content 0
Excel 2013: AutoFilter and SUM() 1
ISO best steps to export (import) all from Outlook 2003 to Outlook in MS 365 0
List all tables, including document structure 0

Top