开发者

Use date as a criteria in SUM or sumif function

开发者 https://www.devze.com 2023-04-02 12:00 出处:网络
I have two columns AmountDate 开发者_开发百科 10001/01/2000 200001/12/2002 200002/02/2005 15003/02/2000

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

精彩评论

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