Using formulas/functions in the Criteria part of SUMIF function

[jardenp]

A	B	C	D	E
1/1/2012	1		1/1/2012	Expected result: 4 (1+3)
1/8/2012	3		2/1/2012	Expected result: 12 (7+5)
2/2/2012	5		3/1/2012	Expected result: 9 (9)
2/7/2012	7		1/1/2013	Expected result: 11 (11)
3/2/2012	9		2/1/2013	Expected result: 28 (13+15)
1/2/2013	11
2/3/2013	13
2/4/2013	15

<tbody>
</tbody>

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.