Welcome!

COUNTIF Formula, exclude "FALSE" values

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!

Find more posts tagged with

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Slow loading
Helloe Everyone! I’m trying to reference another sheet in Smartsheet, but I’ve noticed that sometimes it loads quickly, while other times it takes a very long time to display the list. Does anyone know why this happens? Is there a way to reduce how often this issue occurs? Today, I can’t get anything to load at all. 😑
Report that includes multiple sheets
Hello - Is there a work around in a Report to list multiple sheets in an order that I choose? Sorting doesn't give me the order in which I want them to appear in the report or in a widget on a dashboard. I would like them in a specific order. Thank you.
Revise Filter in Dashboard
I need to revise/expand a filter - the list of items in the filter NOT the filter name - in a dashboard I created. Is this done through the dashboard or through the main data sheet?