COUNTIF & 'not equal to'

Is there a way to use the COUNTIF function and have a cell counted only if it's NOT equal to a specific value?

I'm tracking a weekly metric. I have several rows with a formula that calculates the % increase of the current week's value vs. the prior week. Since there aren't any values in the future weeks yet, the formula comes back with ""#DIVIDE BY ZERO" . I'd like to use the COUNTIF function to only count rows that have a my metric value in it, but not the rows with ""#DIVIDE BY ZERO" .

Right now, I have just the COUNT function in there, which is counting the error cells (for a total of 51). But I'm aiming to get the "# of weeks completed" row to count only 4 (cells showing n/a, 73.46%, 5%, and -100%). And then when I fill in the next row next week, it would count 5 cells, and so on.

Thanks for any help!

Kate

Find more posts tagged with

Accepted answers

All comments

BullandKhmer

Hi There,

You could use either

COUNTIF(range,<>"#DIVIDE BY ZERO")

COUNTIF(range, NOT("#DIVIDE BY ZERO")

You could also use the ISNUMBER FUNCTION

COUNTIF(ISNUMBER(range))

Not sure about the syntax on that last one!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.