Thursday, April 18, 2013

yogi_Row By Row Conditional Sum Of Version Items By Each Of Multiple Type Columns


                                          Google Spreadsheet   Post  #1142
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 18, 2013
user isabeld :(http://productforums.google.com/forum/?zx=pllgikwd361c#!category-topic/docs/spreadsheets/EbdwzFMOsTY)
Counting 2 variables in separate worksheet
Using OSX 10.8 on mac.

I have a spreadsheet with columns for "version" and occurrences of a certain issues in 6 additional columns. Sample data: https://docs.google.com/spreadsheet/ccc?key=0AinwgNgi9t3wdEhoUzdiYmxWeVhsZE1kOEh6cDE4ZWc&usp=sharing

I'd like to pull total # of occurences for each issue by version into a separate sheet, referencing values from the master sheet. take look at "summary" sheet for the desired totals.

Here's what I have so far: =ArrayFormula(sum(regexmatch(master!C:C;"\bpilot\b"))) - this only gives me the sum for each version but doesn't account for the second variable.

I need to add the second condition which counts the sum of #s in each column E-J in the master sheet.

So I'd get for example, in 'summary' B2, a count of all numbers in column E where 'pilot' also occurs in column B.

I hope this makes sense and someone can help me out - thanks!
----------------------------------------------------------------------------------------------------------------------------

following is a solution to the problem