Here's a copy of the Google Sheets I'm working on and here's what I'm trying to do:

I need a COUNTIF formula that would count as 1 if the sum from columns R to AD in the Failed sheet is less than 8. There are five potential responses to columns R to AD and each is assigned a weight:

0 - Not at all  
1 - To a slight degree 
2 - To a moderate degree 
3 - To a great degree 
4 - All the time

Based on the current data, row 30 in the Reports sheet should count as 2.

But how do I do that?

  • Thanks @pnuts. I'll try that. But I've also come up with another formula (please see the spreadsheet) although it's quite lengthy. Would be great if you could show me a better way to do it.
    – Vee
    Feb 4, 2017 at 2:55

1 Answer 1


It looks like the correct countif formula is in your reports sheet right now - but in answer to your comment - if you want to simplify that huge if statement, you can use arrayformula, and vlookup or substitute to trim the formula to this:

=if(istext(B2), SUM(ARRAYFORMULA(VLOOKUP(R2:AD2,{"Not at all",0;"To a slight degree",1;"To a moderate degree",2;"To a great degree",3;"All the time",4},2,FALSE))),)

for my example i gave it a short lookup table, but i included it as a literal array so that you dont have to create a helper column

enter image description here


enter image description here


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.