Use ARRAYFORMULA with horizontal COUNTIF

2
votes

I am using COUNTIF to count the number of occurrences of a value in a horizontal range, which is working fine:

enter image description here

Now I would like to use ARRAYFORMULA to automatically apply that logic to an entire column. My problem is that ARRAYFORMULA doesn't seem to work well with COUNTIF as my range spans both horizontally and vertically. I tried several things:

  • =ARRAYFORMULA(IF(COUNTIF(B2:D,"Passed")=3,"Passed","Failed")) : the formula doesn't even replicate across the column
  • =ARRAYFORMULA(IF(ISBLANK(B2:B),"",IF(COUNTIF(B2:D,"Passed")=3,"Passed","Failed"))) : using the extra IF(ISBLANK(B2:B) trick solves the above issue, but the results are wrong

enter image description here

How can I apply ARRAYFORMULA to a formula containing a horizontal COUNTIF?

Here is a link to the spreadsheet if you want to play around

1
google-sheetscountifarray-formulas
I have exactly the same problem: in a each row I should count occurrences of a symbol in a given horizontal range, using this formula: =if(countif(V2:X2,"■") = 1,Acorn1,if(countif(V2:X2,"■") = 2,Acorn2,if(countif(V2:X2,"■") = 3,Acorn3,""))). What arrayformula should I use to expand vertically? – Riccardo
Did you find a solution for this? – Riccardo
Not on my todo list anymore. You can check @pnuts ' answer and let me know if it works, that way I will accept it – Max

1 Answers

0
votes

An alternative: 

=if(arrayformula(len(B2:B)*(B2:B="P")*(C2:C="P")*(D2:D="P")),"Passed","Failed")