Avoid counting Blank cells as "0"
 

Hi I am using following formula:

=SUMPRODUCT((import!$N$1:INDEX(import!$N$1:$N$3500 0;COUNT(import!$A$1:$A
$35000))0)*(import!$N$1:INDEX(import!$N$1:$N$3500 0;COUNT(import!$A$1:$A
$35000))<=3))

My problem is that 0 does not count cells with 0 but only larger than 0. If
I change it to =0 it sees all blank cells within N1:N35000 as 0.

How can I avoid this????

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200901/1

Avoid counting Blank cells as "0"
 

try to exclude 0 from yr formula

and use COUNT(import!$A$1:$A$35000) simply


On 19 Sty, 11:48, "micro1000 via OfficeKB.com" <u48676@uwe wrote:
Hi I am using following formula:

=SUMPRODUCT((import!$N$1:INDEX(import!$N$1:$N$3500 0;COUNT(import!$A$1:$A
$35000))0)*(import!$N$1:INDEX(import!$N$1:$N$3500 0;COUNT(import!$A$1:$A
$35000))<=3))

My problem is that 0 does not count cells with 0 but only larger than 0. If
I change it to =0 it sees all blank cells within N1:N35000 as 0.

How can I avoid this????

--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200901/1

Avoid counting Blank cells as "0"
 

=SUMPRODUCT(
(import!$N$1:INDEX(import!$N$1:$N$35000,COUNT(impo rt!$A$1:$A$35000))=0)
*(import!$N$1:INDEX(import!$N$1:$N$35000,COUNT(imp ort!$A$1:$A$35000))<=3)
*(import!$N$1:INDEX(import!$N$1:$N$35000,COUNT(imp ort!$A$1:$A$35000))<""))

--
__________________________________
HTH

Bob

"micro1000 via OfficeKB.com" <u48676@uwe wrote in message
news:9069dbda7d9d0@uwe...
Hi I am using following formula:

=SUMPRODUCT((import!$N$1:INDEX(import!$N$1:$N$3500 0;COUNT(import!$A$1:$A
$35000))0)*(import!$N$1:INDEX(import!$N$1:$N$3500 0;COUNT(import!$A$1:$A
$35000))<=3))

My problem is that 0 does not count cells with 0 but only larger than 0.
If
I change it to =0 it sees all blank cells within N1:N35000 as 0.

How can I avoid this????

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200901/1

Avoid counting Blank cells as "0"
 

did not think there might be any negative numbers, sorry
;-)

On 19 Sty, 12:26, "Bob Phillips" wrote:
=SUMPRODUCT(
*(import!$N$1:INDEX(import!$N$1:$N$35000,COUNT(imp ort!$A$1:$A$35000))=0)
*(import!$N$1:INDEX(import!$N$1:$N$35000,COUNT(imp ort!$A$1:$A$35000))<=3)
*(import!$N$1:INDEX(import!$N$1:$N$35000,COUNT(imp ort!$A$1:$A$35000))<""))

--
__________________________________
HTH

Bob

"micro1000 via OfficeKB.com" <u48676@uwe wrote in messagenews:9069dbda7d9d0@uwe...



Hi I am using following formula:

=SUMPRODUCT((import!$N$1:INDEX(import!$N$1:$N$3500 0;COUNT(import!$A$1:$A
$35000))0)*(import!$N$1:INDEX(import!$N$1:$N$3500 0;COUNT(import!$A$1:$A
$35000))<=3))

My problem is that 0 does not count cells with 0 but only larger than 0.