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

The lowest of all? 6
SUM - IF - AND 7
Matching within results of a match 1
sum of values between two given dates 6
Excel 2013: AutoFilter and SUM() 1
summing two columns of different data 4
Formula for summing purchases and sales 8
Formulas to (a) pull month, year and (b) sum within date range? 1

Top