Array Formula

[vanlenter]

Hello,

I need help with getting rid of an array formula... it makes my sheet VERY slow....

essentially what I am trying to do is get an aging to "Count" an item based on the aging....

my formula is as follows:

{=sum(if(Data_out>=H1,if(Data_out<=H2,if(Data_Plng=1,1,"")))}

The only way I've been able to get this to work is by using this formula (did from memory may not be exactly correct here but it does work...

Does anyone know how to get around the Array Formula? They make my sheet to slow to use...

Thanks.

 

[Haseeb Avarakkan]

Hi,

Use the database function like DCOUNTA

 

[T. Valko]

Hello,

I need help with getting rid of an array formula... it makes my sheet VERY slow....

essentially what I am trying to do is get an aging to "Count" an item based on the aging....

my formula is as follows:

{=sum(if(Data_out>=H1,if(Data_out<=H2,if(Data_Plng=1,1,"")))}

The only way I've been able to get this to work is by using this formula (did from memory may not be exactly correct here but it does work...

Does anyone know how to get around the Array Formula? They make my sheet to slow to use...

Thanks.

If you're using Excel 2007 or later...

=COUNTIFS(Data_out,">="&H1,Data_out,"<="&H2,Data_Plng,1)

 

[mmyett]

If you are in a pre 2007 version of Excel you might try using the SumProduct function. I'm not an expert but my experience with DataBase functions are they are slow, even with small data sets.

 

[vanlenter]

Hello Everyone,

Thanks so much for your help. I was able to get to work!!!

Thanks again.