Wednesday, July 24, 2013

Google Spreadsheet Arrayformula Solution


by Wood County Systems )

I am trying to calculate totals for a survey about computer ordering.
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:
The Double Unary does not work in Sheets.  Please help.


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: 


No comments:

Post a Comment