Forum Discussion
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 each requirement are YES, WAIVER, or NO. Both YES and WAIVER count as the requirement being met.
I need to determine the percentage of those 5 requirements as being met.
The formula I tried is =COUNTIF(K5:O5,{ "YES", "WAIVER"})/COUNTA(K5:O5)
Unfortunately I keep getting an error saying that I've entered too few arguments for the function. If I try copy and pasting the code from elsewhere it turns into #SPILL!
I have also tried =COUNTIF(K5:O5, "YES")+COUNTIF(K5:O5,"WAIVER")/COUNTA(K5:O5) which somehow results in 400%, even if I have one of the requirements listed as not being met.
What am I missing?
4 Replies
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), "")- Is it possible for you to post a copy of the actual file (or, if the actual contains confidential information, a mockup that faithfully represents sufficient rows for a test) on OneDrive or GoogleDrive with a link pasted here that grants access?
See if this works when you add more rows. (For future reference, when somebody requests a sample database with sufficient rows for a test, that would normally mean more than a single row. This was simple enough that I'm pretty sure it'll suffice.)
=(COUNTIF(K5:O5,"Yes")+COUNTIF(K5:O5,"Waiver"))/COUNTA(K5:O5)
