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!
Comments

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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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 Preflight Build (Unit)", BSF228:BSF235) / (COUNTIFS(BSF228:BSF235, <> "FALSE", [Primary Column] 228:[Primary Column]235, <> "SCORE Preflight Build (Unit)") * 3)
I hope this helps!
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 438 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 451 Show & Tell
 30 Member Spotlight
 1 SmartStories
 282 Events
 32 Webinars
 7.3K Forum Archives