1

I am trying to make a basic countif function which is =COUNTIF(C2:BE2,"Yes") into an ArrayFunction which is 

=ArrayFormula(if(isblank(A2:A),"",countif(C:BE,"Yes")

But this function counts every single "Yes" in the range C:BE.

How do I make it so it counts row by row and still is an array formula?

Meaning the cell with this function counts from C2:BE2 and the next one counts from C3:BE3

Also I can't use the sign function as it only works if the criteria has numbers in it which the other posts in this forum have.

UzumakiNavin
  • 11
  • 1
  • 4

1 Answers1

2

As an alternative to MMULT (see Max's link), try entering in row 2

=ArrayFormula(if(len(A2:A), countif(if(C2:BE="Yes", row(C2:C)), row(C2:C)),))
JPV
  • 26,499
  • 4
  • 33
  • 48
  • Hi JPV, Thanks for the response but your forumla isn't working. The sheet keeps trying to load the formula but is not loading it or processing it. – UzumakiNavin Aug 29 '18 at 16:59
  • https://docs.google.com/spreadsheets/d/1a3-DzI8gC5xfMEGqhr8ixkbK6Rn-Y9i4l9s8kTC055k/edit?usp=sharing The formula needs to be in sheet 2 – UzumakiNavin Aug 30 '18 at 11:15
  • Hi, you currently have +15000 rows. If you cut down the number of rows and enter in BF1 ={"Total No. of Yes"; ArrayFormula(if(len(A2:A), countif(if(C2:BE="Yes", row(C2:C)), row(C2:C)),))} you should be okay. – JPV Aug 30 '18 at 13:52
  • Hi JPV, I can't thank you enough. That worked! Thanks a lot!! – UzumakiNavin Aug 30 '18 at 17:27
  • @UzumakiNavin: most welcome. Please 'accept' the answer or upvote if the provided formula helped you out. – JPV Aug 30 '18 at 20:00
  • Hi @JPV could you please help with a similar item: https://stackoverflow.com/questions/60105388/counting-incrementing-decrementing-in-google-sheets-using-arrayformula-countif – user2030613 Feb 07 '20 at 03:49