Sumproduct Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Stricklen - 28 Apr 2008 14:51 GMT

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

Reply to this Message

Ron Coderre - 28 Apr 2008 15:15 GMT

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)

> 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))
[quoted text clipped - 4 lines]
>
> David

Reply to this Message

Rate this thread: