Conditional summing problemo

  • Thread starter Thread starter Nigel graham
  • Start date Start date
N

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
 
Nigel graham said:
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.
Click to expand...

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)
 
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)
Click to expand...
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 !!
 
Yep - just found a couple of the so and sos right at the
end of the data !!

Thanks for that.
 
Back
Top