开发者

Use date as a criteria in SUM or sumif function

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)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜