#NUM! error due to changing cells a SUMPRODUCT refers to

  • Can't seem to figure out why the following error occurs:

    I asked you kind chaps for some help a while back, see http://www.ozgrid.com/forum/showthread.php?t=50041

    The formula works wonderfully. Adding new donors is no problem whatsoever. However, if I change the 1st Month of a payment (eg, because someone has increased their donation, which'll kick in at a later date) then i get a #NUM! error in the formula.


    Any ideas?

  • Re: #NUM! error due to changing cells a SUMPRODUCT refers to

    the formula is:

    =SUMPRODUCT(('List of DD Donors'!$D2:$D5024)*('List of DD Donors'!$E2:$E5024='DD Tally'!C5)*IF(DATEDIF(('List of DD Donors'!$F2:$F5024)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!$F2:$F5024)-1,DATE(2006,3,31),"m")))

    Column D contains the Quantity donated, E contains the Frequency (in this case, monthly) and F contains the date of the first payment.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!