MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Complex formula....


Posted by Jack in the UK on February 15, 2002 2:50 AM

Hi guys. im a bit stuck poss my friends can help...

=sumif($N$5:$N2236,"<>PAID",E$5:E$2236)
All fine but i need 2 or better 3 conditions to read sumif
<> "PAID" OR
<>"Current" OR
or the hard bit "Due ......" some date i input say 15.02.02 wich could be any date really.

Any ideas guys..

Cheers
Jack in the UK


Posted by Aladin Akyurek on February 15, 2002 6:15 AM

Jack --

Try:

=SUM(E$5:E$2236)-SUMPRODUCT((($N$5:$N2236="PAID")+($N$5:$N2236="Current"))*(E$5:E$2236))

This does not have the "due" bit in conjunction with a date condition, because you didn't mention any range containing dates.

Aladin

=============

Posted by Chris D on February 15, 2002 6:40 AM

Hiya Jack,

would this also help ? :

=SUMPRODUCT(($n$5:$n$2236<>"PAID")*($n$5:$n$2236<>"Current")*($n$5:$n$2236<>F1),($e$5:$e$2236))

where F1 houses your required due date

I tested it a bit and it seems to work, so hope it helps also

Chris

Posted by Jack in the UK on February 15, 2002 6:46 AM

Aladin .. date is same rang as PAID Currect...thanks [NT]

Posted by Jack in the UK on February 15, 2002 6:47 AM

Nice one Chris D.. ill try and mail you [NT]

Posted by Aladin Akyurek on February 15, 2002 9:35 AM

Would you

post 5 rows of the relevant data including labels? If so inclined,type = in an empty cell, select 5 rows of the range of interest, hit F9, copy everyhing between braces that you see on the Formula Bar, and paste it in the follow up. Would you restate what the criteria are along with what is desired?

========

Posted by Jack in the UK on February 15, 2002 12:04 PM

Aladin -- cool formula PERFECT im fine a big thanks [NT]