Tuesday, July 9, 2013

Google Spreadsheet Arrayformula to Count No in Columns

Question:

( by Alex Vaughan )

Hi 

I have a form with 6 questions answers will be Yes/No

So row 1 will be the question and in row 2 I have the a category of question - Major & Minor. New forms are submitted into row 3. I want to count on each row the number of "No" replies under the Major heading. I can do this using sum product and entering the formula into column H of the new row but when I try to use an array formula to populate it will not copy down the continue function. 

I have attached an image:


This is a simplified version of my problem and any help would be greatly appreciated! 

Thanks
Alex


Solution:

Have a look at the following screenshot of my spreadsheet:


As we have Major's in Column B, D, F and G, so we have to count "No" in these columns.

So, I have inserted following formula in Cell H1:

=arrayformula(if(row(A:A)=1;"Count Major No's";if(row(A:A)=2;"";if(A:A="";"";(B:B="No")+(D:D="No")+(F:F="No")+(G:G="No")))))


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,


No comments:

Post a Comment