I have the following formula entered into multiple cells:

=SUM(IF('2009 Actual'!$C$3:$C$2063=$A6,IF('2009
Actual'!$P$3:$P$2063=$B6,'2009 Actual'!Q$3:Q$2063,0),0))

For two sets of these formulas I have an N/A result, but I know for a fact
that I should get a value. For the other four sets I have a value as
expected. I also have similar setups on four spreadsheets which are mirror
images but are different business units, unfortunately I can't be too
specific as it's confidential data. I've tried using formula auditing and
error checking but they didn't tell me anything! I noticed when I tried to
re-write the formula from scratch using the wizard it complained that the
formula was too large though?

Any help appreciated, I can give more detail about the formulas if needed
but I don't know what anyone will need to know to answer!!!

Thanks
Pat

Hi,
you can replace the formula with =SUMPRODUCT(('2009
Actual'!$C$3:$C$2063=$A6)*('2009
Actual'!$P$3:$P$2063=$B6),'2009 Actual'!Q$3:Q$2063)

HTH

Pat MAAT;288665 Wrote:
I have the following formula entered into multiple cells:

=SUM(IF('2009 Actual'!$C$3:$C$2063=$A6,IF('2009
Actual'!$P$3:$P$2063=$B6,'2009 Actual'!Q$3:Q$2063,0),0))

For two sets of these formulas I have an N/A result, but I know for a
fact
that I should get a value. For the other four sets I have a value as
expected. I also have similar setups on four spreadsheets which are
mirror
images but are different business units, unfortunately I can't be too
specific as it's confidential data. I've tried using formula auditing
and
error checking but they didn't tell me anything! I noticed when I tried
to
re-write the formula from scratch using the wizard it complained that
the
formula was too large though?

Any help appreciated, I can give more detail about the formulas if
needed
but I don't know what anyone will need to know to answer!!!

Thanks
Pat


--
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=80664

I am not following very well, would you post a sample and your expected result
Is your source data in different sheet?

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Pat MAAT" wrote:

I have the following formula entered into multiple cells:

=SUM(IF('2009 Actual'!$C$3:$C$2063=$A6,IF('2009
Actual'!$P$3:$P$2063=$B6,'2009 Actual'!Q$3:Q$2063,0),0))

For two sets of these formulas I have an N/A result, but I know for a fact
that I should get a value. For the other four sets I have a value as
expected. I also have similar setups on four spreadsheets which are mirror
images but are different business units, unfortunately I can't be too
specific as it's confidential data. I've tried using formula auditing and
error checking but they didn't tell me anything! I noticed when I tried to
re-write the formula from scratch using the wizard it complained that the
formula was too large though?

Any help appreciated, I can give more detail about the formulas if needed
but I don't know what anyone will need to know to answer!!!

Thanks
Pat

Do you mean the text value N/A or the error value #N/A. If you mean the
latter, the formula will return #N/A if Column C or Column P contain the
error value #N/A. Is this the case?

Hope this helps!

http://www.xl-central.com

In article ,
Pat MAAT wrote:

I have the following formula entered into multiple cells:

=SUM(IF('2009 Actual'!$C$3:$C$2063=$A6,IF('2009
Actual'!$P$3:$P$2063=$B6,'2009 Actual'!Q$3:Q$2063,0),0))

For two sets of these formulas I have an N/A result, but I know for a fact
that I should get a value. For the other four sets I have a value as
expected. I also have similar setups on four spreadsheets which are mirror
images but are different business units, unfortunately I can't be too
specific as it's confidential data. I've tried using formula auditing and
error checking but they didn't tell me anything! I noticed when I tried to
re-write the formula from scratch using the wizard it complained that the
formula was too large though?

Any help appreciated, I can give more detail about the formulas if needed
but I don't know what anyone will need to know to answer!!!

Thanks
Pat