Nested countif's frying my brain

What I want is the following scenario:

If all values are "Pass" then display "Passed"
If all values are "Fail" then display "Failed"
If all values are "Incomplete" then display "Not Started"
If all values are blank/empty then display "No Use Cases"
If any combination then display "In Progress"

I'm like 85% there but I lost my inception top so I forget where I am.

So far I have the following formula:

=IF(COUNTIF(G:G,"Pass"),"PASSED",IF(COUNTIF(G:G,"Fail"),"FAILED",IF(COUNTIF(G:G,"Incomplete"),"IN PROGRESS","NO USE CASES")))

Please send help.

Devil's Advocate

Posted 2016-02-11T15:29:37.753

Reputation: 1 397

Answers

I believe the following line will solve your problem as I understand it.

=IF(COUNTA(G:H)=0,"NO USE CASES",IF(COUNTIF(G:H,"Pass")=COUNTA(G:H),"PASSED",IF(COUNTIF(G:H,"Fail")=COUNTA(G:H),"FAILED",IF(COUNTIF(G:H,"Incomplete")=COUNTA(G:H),"NOT STARTED","IN PROGRESS"))))

The process I took was to create a simple test excel sheet and just use a couple of variables in rows g and h. The key thing I found was that countif maybe a bit misnamed, it returns the number of matches. In the original formula it would return true if there was at least one 'Pass' and then stop any further processing. Using the counta function, it checks to see if all entries are Pass, if that is true it return PASSED.

Interestingly, this would also return true if there were no Pass entries. So, the first thing the formula does is use counta to check how many non-blank cells there are. If there are 0 then it returns 'No Use Cases', otherwise it goes through processing the rules.

Bryan

Posted 2016-02-11T15:29:37.753

Reputation: 138

I think this does everything! Note that due to a header I had to change the first 0 to a 1 and then add -1 to all the counta statements. – Devil's Advocate – 2016-02-11T16:05:54.573

Try this instead:

=IF(COUNTIF(G:H,"Pass")=COUNTA(G:H),"PASSED",IF(COUNTIF(G:H,"Fail")=COUNTA(G:H),"FAILED",IF(COUNTIF(G:H,"Incomplete")=COUNTA(G:H),"IN PROGRESS","NO USE CASES")))

Explanation:

IF requires logical test but instead you used only COUNTIF(G:H,"something") which returns the number of instances of the word. The suggested formula compares that number to the total number of cells containing any data.

kbo

Posted 2016-02-11T15:29:37.753

Reputation: 111

I tested a couple scenarios and with both it stills says no use cases – Devil's Advocate – 2016-02-11T15:47:05.473

I should probably mention that G:H is a formula not an explicit value. Not sure if that is causing the issue – Devil's Advocate – 2016-02-11T15:49:03.257

I also just discovered another issue. I'm actually only worried about column G. Not sure why the original formula had G:H but I have changed it. Sorry. – Devil's Advocate – 2016-02-11T15:50:32.003

I tried yours with G:G and still get NO USE CASES – Devil's Advocate – 2016-02-11T15:50:59.320

1If you have header row or other values, which should not be considered by the formula, you should account for them. If its 1 cell / row you should subtract 1 from COUNTA(G). If 2 header rows -> substract 2, etc. – kbo – 2016-02-11T15:52:15.887

Just figured that out, I do have a header. Had to do COUNTA(G:G)-1 and now it's better. I think I still need a tweak though – Devil's Advocate – 2016-02-11T15:52:45.167

So I think there's a problem. If there are no use cases, all the expressions evaluate true COUNTIF(G:G,"FAIL") = 0, COUNTA(G:G) = 0. Ideas? – Devil's Advocate – 2016-02-11T15:58:41.300

I think since I'll never be handing out this spreadsheet with no use cases I'll just ignore this little glitch. Here is the current formula which seems to work in every other case: =IF(COUNTIF(G:G,"Pass")=COUNTA(G:G)-1,"PASSED",IF(COUNTIF(G:G,"Fail")=COUNTA(G:G)-1,"FAILED",IF(COUNTIF(G:G,"Incomplete")=COUNTA(G:G)-1,"NOT STARTED",IF(COUNTIF(G:G,"")=COUNTA(G:G)-1,"NO USE CASES","IN PROGRESS")))) – Devil's Advocate – 2016-02-11T16:03:35.283