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),(SUMPRO DUCT((Detail!$A$6:$A$10000=C5)*(Detail!$G$6:$G$100 00=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

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)


"David Stricklen" <dstricklen@kumc.edu> wrote in message
news:48159007.8C8A.004C.0@kumc.edu...
> 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),(SUMPRO DUCT((Detail!$A$6:$A$10000=C5)*(Detail!$G$6:$G$100 00=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



> 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),(SUMPRO DUCT((Detail!$A$6:$A$10000=C5)*(Detail!$G$6:$G$100 00=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

I just decided to do a sumproduct for column O, and then an entirely separate formula for column P, followed by summing those two together. Thanks for your help.


>>> Ron Coderre<ronREMOVETHIScoderre@bigfoot.com> 4/28/2008 9:15 AM >>>

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)


"David Stricklen" <dstricklen@kumc.edu> wrote in message
news:48159007.8C8A.004C.0@kumc.edu...

> 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),(SUMPRO DUCT((Detail!$A$6:$A$10000=C5)*(Detail!$G$6:$G$100 00=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




> 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),(SUMPRO DUCT((Detail!$A$6:$A$10000=C5)*(Detail!$G$6:$G$100 00=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