Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Countif to ignore any errors
I would like to count the number of records which have good data , ignoring
the one's that have any errors for eg: the data in cell A1:a6 is as follows:- aa bb #NUM! cc #NUM! dd The count should give me 4 as only 4 out of the 6 above holds correct data I cannot change the formula that is causing the error (like #NUM! above using ISERROR route) I am using this array formula but not able to get the desired result. {=COUNTIF(IF(ISERROR(A1:A6)=FALSE,D57:D66),"<FALS E")} Any suggestions please Thanks |
#2
|
|||
|
|||
Try
=SUMPRODUCT(--NOT(ISERROR(A1:A6))) <SD wrote in message ... I would like to count the number of records which have good data , ignoring the one's that have any errors for eg: the data in cell A1:a6 is as follows:- aa bb #NUM! cc #NUM! dd The count should give me 4 as only 4 out of the 6 above holds correct data I cannot change the formula that is causing the error (like #NUM! above using ISERROR route) I am using this array formula but not able to get the desired result. {=COUNTIF(IF(ISERROR(A1:A6)=FALSE,D57:D66),"<FALS E")} Any suggestions please Thanks |
#3
|
|||
|
|||
You could try either:
=COUNTIF(A1:A6,"<#NUM!") =SUM(IF(ISERR(A1:A6),0,1)) Art "SD" wrote: I would like to count the number of records which have good data , ignoring the one's that have any errors for eg: the data in cell A1:a6 is as follows:- aa bb #NUM! cc #NUM! dd The count should give me 4 as only 4 out of the 6 above holds correct data I cannot change the formula that is causing the error (like #NUM! above using ISERROR route) I am using this array formula but not able to get the desired result. {=COUNTIF(IF(ISERROR(A1:A6)=FALSE,D57:D66),"<FALS E")} Any suggestions please Thanks |
#4
|
|||
|
|||
This is an alternative, but isn't as good because it doesn't count numbers
unless they are formatted as text (and reentered) or entered as text. =COUNTIF(A1:A6,"*") <SD wrote in message ... I would like to count the number of records which have good data , ignoring the one's that have any errors for eg: the data in cell A1:a6 is as follows:- aa bb #NUM! cc #NUM! dd The count should give me 4 as only 4 out of the 6 above holds correct data I cannot change the formula that is causing the error (like #NUM! above using ISERROR route) I am using this array formula but not able to get the desired result. {=COUNTIF(IF(ISERROR(A1:A6)=FALSE,D57:D66),"<FALS E")} Any suggestions please Thanks |
#5
|
|||
|
|||
Dave R. wrote...
This is an alternative, but isn't as good because it doesn't count numbers unless they are formatted as text (and reentered) or entered as text. =COUNTIF(A1:A6,"*") .... There are times 2 functions make more sense than one. =COUNTIF(range,"*")+COUNT(range) Both function calls skip cells evaluating as error or boolean values. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal And Countif | Excel Discussion (Misc queries) | |||
Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions | |||
Countif | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions |