COUNTIF - #DIV/01

P

PW11111

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
 
A

aristotle

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"))
 
P

PW11111

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
 
A

aristotle

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?
 
P

PW11111

Not sure what you mean.

ISERROR only seems to return True or False, or the default vaule (0) that
you assign it.
 
A

aristotle

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.
 
M

Max

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
 
J

Jerry W. Lewis

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
 
Top