Sumproducts problem

[tlchan]

Hi,

I am using sumproduct to get total for previous and current month from a datasheet. It did not worked as expected in C15 and C16.

Please help.

[onlyadrafter]

Hello,

what results do you expect to see in C15 & C16?

If in C15 you expect 340 then try

=SUMPRODUCT(--(B5:B10>=EOMONTH(B2,-2)+1),(--(B5:B10<=EOMONTH(B2,-1))*C5:C10))

[tlchan]

Hi onlyadrafter,

You are great!

I also expect C16 to have sum of current month report per B2. I have using MONTH function in sumproduct but its total with all the same month inclusive previous year date. The data contained transactions since 2013. Can explain how to get around with EOMONTH function in your solution?


Thanks

[onlyadrafter]

Hello,

For the current month, use

=SUMPRODUCT(--(B5:B10>=EOMONTH(B2,-1)+1),(--(B5:B10<=EOMONTH(B2,0))*C5:C10))

should give an answer of 0 for your data.

[tlchan]

Thanks again onlyadrafter