Not counting based on multiple factors

jblunda

I'm building a formula that uses the contents in a cell to effect a percentage. Basically:

=(count all cells within a range with the word "PASS")

(divide this number by)

(All the cells within same range that are NOT blank )

The piece of the formula that looks at the row and avoids blank cells looks like this:

(COUNTIF(cellname1@row:cellname2@row, (NOT(ISBLANK), @cell)))

When I execute this formula, it still manages to count those blank cells. What am I missing?

Find more posts tagged with

Formulas

logic

Accepted answers

ker9

Hi @jblunda

COUNTM will count the number of elements in non blank cells or range

=COUNTM(Column1@row:Column2@row)

=COUNTM(Column1:Column1)

Hope this helps!

ker9

@jblunda

See if this works with the additional parameter (change "Column1" to your column name)

=COUNTIF([Column1]:[Column1], "PASS") / COUNTIFS([Column1]:[Column1], <>"", [Column1]:[Column1], <>"N/A")

If the range is 2 columns next to each other:

=COUNTIF([Column1]:[Column2], "PASS") / COUNTIFS([Column1]:[Column2], <>"", [Column1]:[Column2], <>"NA")

This is looking at the entire column, if you need to look at a specific range of rows, add the row numbers like this: [Column1]1:[Column1]7

All comments

ker9

Hi @jblunda

COUNTM will count the number of elements in non blank cells or range

=COUNTM(Column1@row:Column2@row)

=COUNTM(Column1:Column1)

Hope this helps!

jblunda

I apologize, but I neglected to add a condition to my question 🤦‍♂️

=(count all cells within a range with the word "PASS")

(divide this number by)

(All the cells within same range that are NOT blank AND don't contain the word "N/A")

I tried to accomplish this with the following:

=SUM(COUNTM(cellname1@row:cellname2@row,CONTAINS("PASS", @cell)))/(COUNTIF(cellname1@row:cellname2@row, CONTAINS("PASS", "FAIL", @cell)))

I get an "Incorrect" error.

ker9

@jblunda

See if this works with the additional parameter (change "Column1" to your column name)

=COUNTIF([Column1]:[Column1], "PASS") / COUNTIFS([Column1]:[Column1], <>"", [Column1]:[Column1], <>"N/A")

If the range is 2 columns next to each other:

=COUNTIF([Column1]:[Column2], "PASS") / COUNTIFS([Column1]:[Column2], <>"", [Column1]:[Column2], <>"NA")

This is looking at the entire column, if you need to look at a specific range of rows, add the row numbers like this: [Column1]1:[Column1]7

jblunda

It works! Thank you very much, and thank you for teaching me something new!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.