| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to avoid "insert copied cells"? | Excel Discussion (Misc queries) | |||
| counting non blank cells for "sum(if" formula | Excel Worksheet Functions | |||
| In excel counting cells in a range which meet condition "Xand<X" | Excel Worksheet Functions | |||
| How to fill in "BLANK" Cells Automactically...Large Spread Sheet | Excel Discussion (Misc queries) | |||
| Changing "returned" values from "0" to "blank" | Excel Worksheet Functions | |||
Linear Mode
