Sumproduct Query

[Guest]

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$27>0))

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?

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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

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)

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$27>0))

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 Phillips]

It treats any text as >0, even a simple =SUMPRODUCT(--(A1:A20>0)) 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)

Bob,
Any explanation for my experience i.e. blank (space) treated as

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

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)

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$27>0))

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?

 

[Guest]

Thanks Bob.

It treats any text as >0, even a simple =SUMPRODUCT(--(A1:A20>0)) 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)

Bob,
Any explanation for my experience i.e. blank (space) treated as

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

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)

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$27>0))

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?

 

[Guest]

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

Thanks Bob.

It treats any text as >0, even a simple =SUMPRODUCT(--(A1:A20>0)) 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)

Bob,
Any explanation for my experience i.e. blank (space) treated as

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

:

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)

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$27>0))

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?