Google Spreadsheet Arrayformula Solution

Question:

( by Wood County Systems )

I am trying to calculate totals for a survey about computer ordering.

 

DATA:

Current Computer Type	Current Number of Monitors	Current Keyboard Type	New Computer Type	New Number of Monitors	New Type of Keyboard
Laptop	2	Straight	No Change	No Changes	No Changes
Desktop	1	Straight	Desktop to Laptop	1 Monitor to 2 Monitors	Change to Ergonomic
Desktop	1	Ergonomic	Desktop to Laptop	1 Monitor to 2 Monitors	Change to Straight
Desktop	1	Ergonomic	No Change	No Changes	No 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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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,

Kishan.

iGoogleDrive