I have two columns
Amount Date
开发者_开发百科
100 01/01/2000
2000 01/12/2002
2000 02/02/2005
150 03/02/2000
250 05/03/2002
350 04/05/2006
I need result as follows
Total year
2005 - 2000
2000 - 250
2002 - 2250
2006 - 350
Can I use SUMIF function for this? If it's possible what criteria I have to put?
You can use the same formula as described in this thread: Summarize grouping by year and month
=SUMPRODUCT((YEAR(Sheet1!$B$2:$B$6)=A2)*Sheet1!$A$2:$A$6)
I supposed your data are in an array from A1
to A6
on Sheet1
and your results are on another Sheet, you put the year in column A
and the formula in column B
If you add a column to the right of the date column containing
=year(B2)
(replace B2 with whichever cell contains the date in that row) and fill that column down, then you can do a standard sumif on that column like
=sumif(C2:C10, 2002, A2:A10)
精彩评论