Sumproduct Help

[David Stricklen]

I have the following formula,
=IF(G5=2000,(SUMPRODUCT((Detail!$A$6:$A$10000=C5)*(Detail!$E$6:$E$10000=G5)*-1,Detail!$O$6:$O$10000)),IF(OR(E5=6,E5=21),(SUMPRODUCT((Detail!$A$6:$A$10000=C5)*(Detail!$G$6:$G$10000=G5),Detail!$O$6:$O$10000)),SUMPRODUCT((Detail!$A$6:$A$10000=C5)*(Detail!$G$6:$G$10000=G5),Detail!$O$6:$O$10000)*-1))

which works perfectly fine, but instead of just summing the O column, I would like it to sum O and P. However when I change it to Detail!$O$6:$P$10000, it gives me a #Value error. Any suggestions?

David

 

[Ron Coderre]

Try something like this:

=IF(G5=2000,-1,IF(OR(E5={6,21}),1,-1))*
SUMPRODUCT((Detail!$A$6:$A$10000=C5)*
(Detail!$G$6:$G$10000=G5)*Detail!$O$6:$O$10000)

Am I on the right track?
Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)