Conditional summing problemo

[Nigel graham]

I'm using excel 2000 (SR1)and am having a problem with
this formula:
{=SUM(IF(Total!$N$2:$N$2000=H12,IF(Total!
$K$2:$K$2000=3,Total!$O$2:$O$2000,0),0))}

It works up to 2000 lines but beyond that returns #N/A.

I've got quite a few other conditional sums on the
worksheet that are behaving themselves that go to 50,000
and beyond.

I've rebooted and everything but no joy.

Please help !

Thanks

 

[Paul]

I'm using excel 2000 (SR1)and am having a problem with
this formula:
{=SUM(IF(Total!$N$2:$N$2000=H12,IF(Total!
$K$2:$K$2000=3,Total!$O$2:$O$2000,0),0))}

It works up to 2000 lines but beyond that returns #N/A.

I've got quite a few other conditional sums on the
worksheet that are behaving themselves that go to 50,000
and beyond.

I'm not sure why your problem exists, but you could try this alternative
formula that does not need to be array-entered (and calculates faster):
=SUMPRODUCT((Total!$N$2:$N$2000=H12)*(Total!$K$2:$K$2000=3),Total!$O$2:$O$20
00)

 

[J.E. McGimpsey]

Do any of your cells in O2001+ return #N/A?

 

[Guest]

formula that does not need to be array-entered (and calculates faster):
=SUMPRODUCT((Total!$N$2:$N$2000=H12)*(Total! $K$2:$K$2000=3),Total!$O$2:$O$20
00)

Thanks Paul,

The formula works fine but I've now found out what the
problem was with my previous formula - the data the
formula was looking at was generated using other formulas
and contined some #N/A which I missed. I will include an
ISERROR function to replace errors with 0 to make up for
my dodgy eyesight !!

 

[Guest]

Yep - just found a couple of the so and sos right at the
end of the data !!

Thanks for that.