Home |
Search |
Today's Posts |
#1
|
|||
|
|||
COUNTIF - #DIV/01
Hi I have the following formula - =COUNTIF(StatusCheck, "Snag Item")/COUNTIF(ElementCount,"Main Contractor - Builderswork") StatusCheck and ElementCount are named ranges. The formula works - however when nothing is entered on the spreadsheet (the formula has nothing to count) it displays #DIV/01. Is there anything I can add to the formula to stop this happening? Any help would be great. Cheers, Phil |
#2
|
|||
|
|||
Hi, this dhould do it...
=IF(ISERROR(COUNTIF(StatusCheck, "Snag Item")/COUNTIF(ElementCount,"Main Contractor - Builderswork")),0,COUNTIF(StatusCheck, "Snag Item")/COUNTIF(ElementCount,"Main Contractor - Builderswork")) -- We are what we repeatedly do. Excellence, therefore, is not an act, but a habit. "PW11111" wrote: Hi I have the following formula - =COUNTIF(StatusCheck, "Snag Item")/COUNTIF(ElementCount,"Main Contractor - Builderswork") StatusCheck and ElementCount are named ranges. The formula works - however when nothing is entered on the spreadsheet (the formula has nothing to count) it displays #DIV/01. Is there anything I can add to the formula to stop this happening? Any help would be great. Cheers, Phil |
#3
|
|||
|
|||
Hi It does now return 0 instead of #DIV/01 - however it has stoped counting the Main Contractor - Builderswork string. In otherwords its now always 0.! Any ideas? Thanks for the help. Phil "PW11111" wrote: Hi I have the following formula - =COUNTIF(StatusCheck, "Snag Item")/COUNTIF(ElementCount,"Main Contractor - Builderswork") StatusCheck and ElementCount are named ranges. The formula works - however when nothing is entered on the spreadsheet (the formula has nothing to count) it displays #DIV/01. Is there anything I can add to the formula to stop this happening? Any help would be great. Cheers, Phil |
#4
|
|||
|
|||
Really? IF(ISERROR()) should only supress errors. Can you give me an
example of the data values that should present a count other than 0? "PW11111" wrote: Hi It does now return 0 instead of #DIV/01 - however it has stoped counting the Main Contractor - Builderswork string. In otherwords its now always 0.! Any ideas? Thanks for the help. Phil "PW11111" wrote: Hi I have the following formula - =COUNTIF(StatusCheck, "Snag Item")/COUNTIF(ElementCount,"Main Contractor - Builderswork") StatusCheck and ElementCount are named ranges. The formula works - however when nothing is entered on the spreadsheet (the formula has nothing to count) it displays #DIV/01. Is there anything I can add to the formula to stop this happening? Any help would be great. Cheers, Phil |
#5
|
|||
|
|||
Not sure what you mean. ISERROR only seems to return True or False, or the default vaule (0) that you assign it. "aristotle" wrote: Really? IF(ISERROR()) should only supress errors. Can you give me an example of the data values that should present a count other than 0? "PW11111" wrote: Hi It does now return 0 instead of #DIV/01 - however it has stoped counting the Main Contractor - Builderswork string. In otherwords its now always 0.! Any ideas? Thanks for the help. Phil "PW11111" wrote: Hi I have the following formula - =COUNTIF(StatusCheck, "Snag Item")/COUNTIF(ElementCount,"Main Contractor - Builderswork") StatusCheck and ElementCount are named ranges. The formula works - however when nothing is entered on the spreadsheet (the formula has nothing to count) it displays #DIV/01. Is there anything I can add to the formula to stop this happening? Any help would be great. Cheers, Phil |
#6
|
|||
|
|||
The formula dictates that it should return 0 if the ISERROR element evaluates
to true, otherwise it if the ISERROR element evaluates to false, it should continue with the original formula. -- Please rate my response if you found it helpful. Thanks. "PW11111" wrote: Not sure what you mean. ISERROR only seems to return True or False, or the default vaule (0) that you assign it. "aristotle" wrote: Really? IF(ISERROR()) should only supress errors. Can you give me an example of the data values that should present a count other than 0? "PW11111" wrote: Hi It does now return 0 instead of #DIV/01 - however it has stoped counting the Main Contractor - Builderswork string. In otherwords its now always 0.! Any ideas? Thanks for the help. Phil "PW11111" wrote: Hi I have the following formula - =COUNTIF(StatusCheck, "Snag Item")/COUNTIF(ElementCount,"Main Contractor - Builderswork") StatusCheck and ElementCount are named ranges. The formula works - however when nothing is entered on the spreadsheet (the formula has nothing to count) it displays #DIV/01. Is there anything I can add to the formula to stop this happening? Any help would be great. Cheers, Phil |
#7
|
|||
|
|||
Perhaps 2 possible suspect areas:
a. The phrase: Main Contractor - Builderswork in the original post had 2 spaces after the word "Contractor" before the dash "-" while the suggested formula has one space. A mismatch, correctable by inserting the extra space in the suggested formula. b. Uncorrected line wraps / breaks in direct copy pasting from the formula as posted could cause errors as well -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#8
|
|||
|
|||
Assuming that there are no error value in your data ranges,
=IF(denominator,formula,"") or =IF(denominator,formula,0) depending on what you want returned when there is no matching data. IF(ISERROR(formula),formula,"") as previously suggested will work, but unnecessarily calculates the numerator an extra time. Jerry PW11111 wrote: Hi I have the following formula - =COUNTIF(StatusCheck, "Snag Item")/COUNTIF(ElementCount,"Main Contractor - Builderswork") StatusCheck and ElementCount are named ranges. The formula works - however when nothing is entered on the spreadsheet (the formula has nothing to count) it displays #DIV/01. Is there anything I can add to the formula to stop this happening? Any help would be great. Cheers, Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF COMBINATION?? | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions |