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

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

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

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

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

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

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

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