Wednesday, July 24, 2013

Google Spreadsheet Arrayformula Solution

Question:


by Wood County Systems )

I am trying to calculate totals for a survey about computer ordering.
 
DATA:
Current Computer TypeCurrent Number of MonitorsCurrent Keyboard TypeNew Computer TypeNew Number of MonitorsNew Type of Keyboard
Laptop2StraightNo ChangeNo ChangesNo Changes
Desktop1StraightDesktop to Laptop1 Monitor to 2 MonitorsChange to Ergonomic
Desktop1ErgonomicDesktop to Laptop1 Monitor to 2 MonitorsChange to Straight
Desktop1ErgonomicNo ChangeNo ChangesNo Changes
 
In Excel my Fomula looks like this for each cell: =COUNTIF(Sheet1!$A2:$H2, -- AND(Sheet1!$F2="No Change", Sheet1!$C2="Desktop"))
 
My totals look like this for each cell:
0
0
0
1
 
The Double Unary does not work in Sheets.  Please help.

Solution:

Have a look at the following screenshot:



Now, instead of filling formula in each cell, you can use arrayformula to auto compute entire range.

I have inserted the following formula in Cell I2:
=arrayformula(if(C2:C="";"";if((Sheet1!F2:F="No Change")*(Sheet1!C2:C="Desktop");1;0)))


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