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?

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

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!