adding next years value

[tleehh]

i would like to add all the values in month of january of 2010 in colume D.
i am using formula as follows but does not add.

=sumproduct(d10:d100,--(month(b10:b100)=month(now())+2,--(year(b10:b100)=
year(now())+1))

colume B contains a dates and colume D cotains value.

Thank you.

 

[Dave Peterson]

Are you really asking to sum the values for dates two months from now?

So on Jan 13, I'd want March -- of the same year?

if yes:

=sumproduct(d10:d100,
--(text(b1:b10,"yyyymm")
=text(date(year(today()),month(today())+2,1),"yyyymm")))

 

[tleehh]

Are you really asking to sum the values for dates two months from now?

So on Jan 13, I'd want March -- of the same year?

if yes:

=sumproduct(d10:d100,
--(text(b1:b10,"yyyymm")
=text(date(year(today()),month(today())+2,1),"yyyymm")))




--

Dave Peterson
.
Thank you!

 

[David Biddulph]

2 problems.
Firstly your parentheses don't match. You forgot the closing parenthesis
after the figure 2.
Secondly if MONTH(NOW()) is 11, you are looking for MONTH(B10:B100) being
13. We don't usually get 13 months in a year.
You can resolve the second problem by using MOD(...,12)
=SUMPRODUCT(D10100,--(MONTH(B10:B100)=MOD(MONTH(NOW())+2,12)),--(YEAR(B10:B100)=YEAR(NOW())+1))But also see Dave Peterson's question as to whether in Jan 2010 you'd wantMar 2010, or Mar 2011 which your current formula would be looking for.--David Biddulph"tleehh" <[email protected]> wrote in messagewould like to add all the values in month of january of 2010 in colume D.> i am using formula as follows but does not add.>> =sumproduct(d10:d100,--(month(b10:b100)=month(now())+2,--(year(b10:b100)=> year(now())+1))>> colume B contains a dates and colume D cotains value.>> Thank you.