PDA

View Full Version : [SOLVED] Sumproducts problem



tlchan
03-03-2017, 10:02 PM
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
03-04-2017, 09:07 AM
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
03-04-2017, 06:56 PM
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
03-07-2017, 12:21 PM
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
03-08-2017, 07:11 AM
Thanks again onlyadrafter:hi: