## Wednesday, July 24, 2013

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

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,