I am using the following formula to reference another file and count the
number of entries in the array:

=SUMPRODUCT(--('Z:\2006\06''06\[01.06.06.XLS]delays & pax'!$E$7:$E$270))

the above is returning 20, which is a count of the size of the array rather
than the number of entries greater than zero. I have used a similar formula
on the same sheet and it's working perfectly, but however I alter the above,
it only ever returns 20.

Any Ideas?

Blanks (as opposed to null) appear to count as 0 on my testing. Try 'Clear
Contents' on "blank" cells.

Or if there are no cells with 0 then:

=SUMPRODUCT(--ISNUMBER('[01.06.06.XLS]delays & pax'!$E$7:$E$27))

HTH

"shakey1181" wrote:

I am using the following formula to reference another file and count the
number of entries in the array:

=SUMPRODUCT(--('Z:\2006\06''06\[01.06.06.XLS]delays & pax'!$E$7:$E$270))

the above is returning 20, which is a count of the size of the array rather
than the number of entries greater than zero. I have used a similar formula
on the same sheet and it's working perfectly, but however I alter the above,
it only ever returns 20.

Any Ideas?

That should work okay. Do you get the problem with the workbook open,
closed, or both?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"shakey1181" wrote in message
...
I am using the following formula to reference another file and count the
number of entries in the array:

=SUMPRODUCT(--('Z:\2006\06''06\[01.06.06.XLS]delays & pax'!$E$7:$E$270))

the above is returning 20, which is a count of the size of the array
rather
than the number of entries greater than zero. I have used a similar
formula
on the same sheet and it's working perfectly, but however I alter the
above,
it only ever returns 20.

Any Ideas?

Bob,
Any explanation for my experience i.e. blank (space) treated as
0? I would have expected these to be treated as 0.

"Bob Phillips" wrote:

That should work okay. Do you get the problem with the workbook open,
closed, or both?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"shakey1181" wrote in message
...
I am using the following formula to reference another file and count the
number of entries in the array:

=SUMPRODUCT(--('Z:\2006\06''06\[01.06.06.XLS]delays & pax'!$E$7:$E$270))

the above is returning 20, which is a count of the size of the array
rather
than the number of entries greater than zero. I have used a similar
formula
on the same sheet and it's working perfectly, but however I alter the
above,
it only ever returns 20.

Any Ideas?

It treats any text as 0, even a simple =SUMPRODUCT(--(A1:A200)) will count
them. If you want to ignore, it has to be smarter, test for a number, or
length = 0.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Toppers" wrote in message
...
Bob,
Any explanation for my experience i.e. blank (space) treated as

0? I would have expected these to be treated as 0.

"Bob Phillips" wrote:

That should work okay. Do you get the problem with the workbook open,
closed, or both?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"shakey1181" wrote in message
...
I am using the following formula to reference another file and count
the
number of entries in the array:

=SUMPRODUCT(--('Z:\2006\06''06\[01.06.06.XLS]delays &
pax'!$E$7:$E$270))

the above is returning 20, which is a count of the size of the array
rather
than the number of entries greater than zero. I have used a similar
formula
on the same sheet and it's working perfectly, but however I alter the
above,
it only ever returns 20.

Any Ideas?

Thanks Bob.

"Bob Phillips" wrote:

It treats any text as 0, even a simple =SUMPRODUCT(--(A1:A200)) will count
them. If you want to ignore, it has to be smarter, test for a number, or
length = 0.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Toppers" wrote in message
...
Bob,
Any explanation for my experience i.e. blank (space) treated as

0? I would have expected these to be treated as 0.

"Bob Phillips" wrote:

That should work okay. Do you get the problem with the workbook open,
closed, or both?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"shakey1181" wrote in message
...
I am using the following formula to reference another file and count
the
number of entries in the array:

=SUMPRODUCT(--('Z:\2006\06''06\[01.06.06.XLS]delays &
pax'!$E$7:$E$270))

the above is returning 20, which is a count of the size of the array
rather
than the number of entries greater than zero. I have used a similar
formula
on the same sheet and it's working perfectly, but however I alter the
above,
it only ever returns 20.

Any Ideas?

I still can't get this to work, using the ISNUMBER formula above, this is now
returning 0, even though the result should be 10. How do I test for a number?

Thanks


"Toppers" wrote:

Thanks Bob.

"Bob Phillips" wrote:

It treats any text as 0, even a simple =SUMPRODUCT(--(A1:A200)) will count
them. If you want to ignore, it has to be smarter, test for a number, or
length = 0.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Toppers" wrote in message
...
Bob,
Any explanation for my experience i.e. blank (space) treated as

0? I would have expected these to be treated as 0.

"Bob Phillips" wrote:

That should work okay. Do you get the problem with the workbook open,
closed, or both?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"shakey1181" wrote in message
...
I am using the following formula to reference another file and count
the
number of entries in the array:

=SUMPRODUCT(--('Z:\2006\06''06\[01.06.06.XLS]delays &
pax'!$E$7:$E$270))

the above is returning 20, which is a count of the size of the array
rather
than the number of entries greater than zero. I have used a similar
formula
on the same sheet and it's working perfectly, but however I alter the
above,
it only ever returns 20.

Any Ideas?