I have a table of customers items due dates etc and want to use the
conditional sum wizard to sum by account number (Column A, Table5) to sum the
overdue value by no days overdue, this works fine if I enter the account
number in the formula but I would like to enter this account number in a cell
eg A2 so that i do not have to keep changing the formula. Any help would be
greatly appreciated. Thanks

Not working
=SUM(IF($A$20:$A$20000="A2",IF($L$20:$L$20000=1,I F($L$20:$L$20000<=30,$G$20:$G$20000,0),0),0))

Working formula
=SUM(IF($A$20:$A$20000="300099999",IF($L$20:$L$200 00=1,IF($L$20:$L$20000<=30,$G$20:$G$20000,0),0),0 ))

Rich;222543 Wrote:
I have a table of customers items due dates etc and want to use the
conditional sum wizard to sum by account number (Column A, Table5) to
sum the
overdue value by no days overdue, this works fine if I enter the
account
number in the formula but I would like to enter this account number in
a cell
eg A2 so that i do not have to keep changing the formula. Any help
would be
greatly appreciated. Thanks

Not working
=SUM(IF($A$20:$A$20000="A2",IF($L$20:$L$20000=1,I F($L$20:$L$20000<=30,$G$20:$G$20000,0),0),0))

Working formula
=SUM(IF($A$20:$A$20000="300099999",IF($L$20:$L$200 00=1,IF($L$20:$L$20000<=30,$G$20:$G$20000,0),0),0 ))

Hi
Or change to
=SUM(IF($A$20:$A$20000=A2,IF($L$20:$L$20000=1,I
F($L$20:$L$20000<=30,$G$20:$G$20000,0),0),0)) no quotes around A2, or
=SUMproduct(($A$20:$A$20000=A2)*($L$20:$L$20000=1 )*($L$20:$L$20000<=30)*$G$20:$G$20000)


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61465