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