Forum Discussion

busymamabee's avatar
busymamabee
Copper Contributor
Aug 26, 2024

COUNTIF Equation Issues

I'm having trouble with a COUNTIF formula which is trying to determine the percentage of requirements met based on the following:   There are 5 test requirements, ranging from K5:O5. Options for ea...
excel
Formulas and Functions
office 365
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 27, 2024

busymamabee 

I am getting mixed messages from your proposed solutions.  You are using an Excel Table and clearly have a dynamic array version of Excel to get #SPILL! errors.  Yet, your programming style is that of Excel 2003 and earlier.  

 

Tables are designed to grow as you add data so the 400 or so blank rows (just a formula here and there) are not needed.  The summary data would best be held above the table rather than as row 1 so that you can then have consistent formulas.

[To retain the look of a single table you can copy the headings to the top of the sheet '=Table1[#Headers]' and hide the real header row]

 

Using 365 formulas within a table you need to take care that they do not return array results because spilling is blocked within a table.  A possible formula is of the form

= LET(
    req, Table1[@[Reading Test Req.]:[US HIS EOC Req]],
    IFERROR(SUM(COUNTIFS(req, {"Yes","Waiver"})) / COUNTA(req), "")
  )

If the definition of the variable 'req' looks too clunky for taste it could be defined as a Name using Name Manager, in which case the formula would reduce to

= IFERROR(SUM(COUNTIFS(req, {"Yes","Waiver"})) / COUNTA(req), "")

 

Resources