COUNTIF Formula, exclude "FALSE" values

molly.schwarzmiller83806

I have a COUNTIF formula that is referencing multiple cells for a calculation depending on tier. If I use this version of the formula, the calculation will correctly exclude counting any cells with a "FALSE" value. If I have to skip a cell (i.e. can't use a colon reference to sum the data), then the formula counts the false values instead of excluding them.

Formula that works:

=IFERROR((SUM(BSF228, BSF229, BSF230, BSF231, BSF232, BSF233, BSF234, BSF235)), "FALSE") / (24 - (COUNTIF(BSF228:BSF235, "FALSE") * 3))

Formula that causes the FALSE values to be counted (in this example BSF234 needs to excluded in addition to any false values):

=IFERROR((SUM(BSF228, BSF229, BSF230, BSF231, BSF232, BSF233, BSF235)), "FALSE") / (21 - (COUNTIF(BSF228 + BSF229 + BSF230 + BSF231 + BSF232 + BSF233 + BSF235, "FALSE") * 3))

I also attached a screen shot for additional reference.In this screenshot, for Tier 2, the formula should exclude the FALSE values and row 234, so total should be 4/6= 67%, because only two of the values should be included, rows 229 and 235 instead of 4/21=19% which is bringing all the FALSE values in the calculation.

Thanks for any help!

Andrée Starå

Hi,

Can you share the sheet(s) or some screenshots with the row numbers and columns? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Alejandra

Hi Molly,

In order for each cell to be evaluated individually within the COUNTIF function, the range must be formatted with a colon —like this BSF228:BSF235.

I think easiest way to get the results you're looking for would be to include another criteria to the COUNTIF making it a COUNTIFS, that will ignore row number 234 based on a criteria.

If I'm understanding this correctly, you may want to try a formula similar to this:

=SUMIF([Primary Column] 228:[Primary Column]235, <> "SCORE Pre-flight Build (Unit)", BSF228:BSF235) / (COUNTIFS(BSF228:BSF235, <> "FALSE", [Primary Column] 228:[Primary Column]235, <> "SCORE Pre-flight Build (Unit)") * 3)

I hope this helps!