Using formulas/functions in the Criteria part of SUMIF function

[jardenp]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/2012[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1/1/2012[/TD]
[TD]Expected result: 4 (1+3)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/8/2012[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]2/1/2012[/TD]
[TD]Expected result: 12 (7+5)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/2/2012[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]3/1/2012[/TD]
[TD]Expected result: 9 (9)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/7/2012[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]1/1/2013[/TD]
[TD]Expected result: 11 (11)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/2/2012[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]2/1/2013[/TD]
[TD]Expected result: 28 (13+15)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2/2013[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/3/2013[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/4/2013[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I'm trying to accomplish is that the cells in Column E return the sum of values in Column B for all rows where the month and year of Column A matches the month and year of the adjacent cell in Column D. For example, The formula in E1 would look for rows in Column A with a month value of 1 and a year value of 2012 and return the sum of Column B values for those rows. In this example, it would be rows 1 and 2.

Would I use SUMIF here? Maybe using some form of AND(MONTH(A)=MONTH(D), YEAR(A)=YEAR(D))?

Thanks.

 

[VoG]

Try

=SUMPRODUCT(--(MONTH(A$1:A$100)=MONTH(D1)),--(YEAR(A$1:A$100)=YEAR(D1)),B$1:B$100)

 

[jardenp]

That works perfectly. Thank you very much.

I'm not familiar with the use of "--" in the formula. What does that do?

 

[VoG]

-- converts an array of False/True to an array of 0/1that Sumproduct can process.

 

[jardenp]

I just experimented and figured out how I can add other criteria. This is great. Thanks VoG. Definitely not the first time you've helped me.