Application hang with array sum(sumif... formula
 

I have an excel sheet that recalc very fast under 2003, under 2007 it never
finish. The formulat that causes the problem is an array sum of a sumif...
What I'm trying to do is sum a column if the values are equal to a range of
othe values... A typical use would be:

={sum(sumif($A:$A, G5:K5, $B:$B))}

what this does under 2003 is sums the values in column B if what's in column
A equals the contents of G5, H5, I5, J5, or K5.

As I understand it the sumif creates an array of the sumif's and then the
array sum sums all those values.

In 2003 blazingly fast, under 2007 even with one such formula it never
completes. If interested I have an example sheeet.

Application hang with array sum(sumif... formula
 

2007 has more rows than 2003. Maybe too many rows.

consider replacing $A:$A
with
$A:$A$1000
or some other suitable upper limit
same for $B:$B
--
Gary''s Student - gsnu200719


"klubar" wrote:

I have an excel sheet that recalc very fast under 2003, under 2007 it never
finish. The formulat that causes the problem is an array sum of a sumif...
What I'm trying to do is sum a column if the values are equal to a range of
othe values... A typical use would be:

={sum(sumif($A:$A, G5:K5, $B:$B))}

what this does under 2003 is sums the values in column B if what's in column
A equals the contents of G5, H5, I5, J5, or K5.

As I understand it the sumif creates an array of the sumif's and then the
array sum sums all those values.

In 2003 blazingly fast, under 2007 even with one such formula it never
completes. If interested I have an example sheeet.

Application hang with array sum(sumif... formula
 

Test your formula with XL2007 (1GHz pentium with 356 MB of RAM). No problem.

"klubar" wrote:

I have an excel sheet that recalc very fast under 2003, under 2007 it never
finish. The formulat that causes the problem is an array sum of a sumif...
What I'm trying to do is sum a column if the values are equal to a range of
othe values... A typical use would be:

={sum(sumif($A:$A, G5:K5, $B:$B))}

what this does under 2003 is sums the values in column B if what's in column
A equals the contents of G5, H5, I5, J5, or K5.

As I understand it the sumif creates an array of the sumif's and then the
array sum sums all those values.

In 2003 blazingly fast, under 2007 even with one such formula it never
completes. If interested I have an example sheeet.

Application hang with array sum(sumif... formula
 

i use the array formular in the excel 2007
=SUMIFS(B:B,A:A,G5:J5)
it calc all right

--
Regards,

Sebation.G
"klubar" ...
I have an excel sheet that recalc very fast under 2003, under 2007 it never
finish. The formulat that causes the problem is an array sum of a sumif...
What I'm trying to do is sum a column if the values are equal to a range
of
othe values... A typical use would be:

={sum(sumif($A:$A, G5:K5, $B:$B))}

what this does under 2003 is sums the values in column B if what's in
column
A equals the contents of G5, H5, I5, J5, or K5.

As I understand it the sumif creates an array of the sumif's and then the
array sum sums all those values.

In 2003 blazingly fast, under 2007 even with one such formula it never
completes. If interested I have an example sheeet.

Application hang with array sum(sumif... formula
 

Tried it... that's not the problem... there's something else going on. Also,
Excel only looks at used cells which is a really fast option.

"Gary''s Student" wrote:

2007 has more rows than 2003. Maybe too many rows.

consider replacing $A:$A
with
$A:$A$1000
or some other suitable upper limit
same for $B:$B
--
Gary''s Student - gsnu200719


"klubar" wrote:

I have an excel sheet that recalc very fast under 2003, under 2007 it never
finish. The formulat that causes the problem is an array sum of a sumif...
What I'm trying to do is sum a column if the values are equal to a range of
othe values... A typical use would be:

={sum(sumif($A:$A, G5:K5, $B:$B))}

what this does under 2003 is sums the values in column B if what's in column
A equals the contents of G5, H5, I5, J5, or K5.

As I understand it the sumif creates an array of the sumif's and then the
array sum sums all those values.

In 2003 blazingly fast, under 2007 even with one such formula it never
completes. If interested I have an example sheeet.

Application hang with array sum(sumif... formula
 

Actually, the sheet is slightly more complex. I simplied the example for the
post. If you'd like to see the real speadsheet PM offline at klubar (AT)
emiboston (DoT) com.

ken


"Teethless mama" wrote:

Test your formula with XL2007 (1GHz pentium with 356 MB of RAM). No problem.

"klubar" wrote:

I have an excel sheet that recalc very fast under 2003, under 2007 it never
finish. The formulat that causes the problem is an array sum of a sumif...
What I'm trying to do is sum a column if the values are equal to a range of
othe values... A typical use would be:

={sum(sumif($A:$A, G5:K5, $B:$B))}

what this does under 2003 is sums the values in column B if what's in column
A equals the contents of G5, H5, I5, J5, or K5.

As I understand it the sumif creates an array of the sumif's and then the
array sum sums all those values.

In 2003 blazingly fast, under 2007 even with one such formula it never
completes. If interested I have an example sheeet.

Application hang with array sum(sumif... formula
 

That would do it, but the sheet needs to be compatible wtih Office 2003.

"Sebation.G" wrote:

i use the array formular in the excel 2007
=SUMIFS(B:B,A:A,G5:J5)
it calc all right

--
Regards,

Sebation.G
"klubar" ...
I have an excel sheet that recalc very fast under 2003, under 2007 it never
finish. The formulat that causes the problem is an array sum of a sumif...
What I'm trying to do is sum a column if the values are equal to a range
of
othe values... A typical use would be:

={sum(sumif($A:$A, G5:K5, $B:$B))}

what this does under 2003 is sums the values in column B if what's in
column
A equals the contents of G5, H5, I5, J5, or K5.

As I understand it the sumif creates an array of the sumif's and then the
array sum sums all those values.

In 2003 blazingly fast, under 2007 even with one such formula it never
completes. If interested I have an example sheeet.

Application hang with array sum(sumif... formula
 

Hi

Even entered as an array formula, that only returns a result from column
B where items in column A match the first criterion - the value in G5.
It ignores H5:J5

--
Regards

Roger Govier


"Sebation.G" wrote in message
...
i use the array formular in the excel 2007
=SUMIFS(B:B,A:A,G5:J5)
it calc all right

--
Regards,

Sebation.G
"klubar"
...
I have an excel sheet that recalc very fast under 2003, under 2007 it
never
finish. The formulat that causes the problem is an array sum of a
sumif...
What I'm trying to do is sum a column if the values are equal to a
range of
othe values... A typical use would be:

={sum(sumif($A:$A, G5:K5, $B:$B))}

what this does under 2003 is sums the values in column B if what's in
column
A equals the contents of G5, H5, I5, J5, or K5.

As I understand it the sumif creates an array of the sumif's and then
the
array sum sums all those values.

In 2003 blazingly fast, under 2007 even with one such formula it
never
completes. If interested I have an example sheeet.