Thursday, September 20, 2012

yogi_Count And Average Values For Multiple Criteria From Data In Another Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #765   Sep 20, 2012     www.energyefficientbuild.com

user chrisB_ofk said: (http://productforums.google.com/forum/?zx=y7jq6icx61c4#!category-topic/docs/spreadsheets/nN1NcXBbx2c)
Countif with multiple criteria and indirect references
I'm trying to set up a formula to count the number of responses meet two different criteria from another sheet in a workbook.  I can't use countifs and I've tried all kinds of combinations of sumproducts, sum if, and what not.
Here are the two formulae:
=countif(indirect($A$2&"!B:B"),A5) <-- This works just fine and returns the appropriate number of responses where the rows meet the variable in A5.
=countif(indirect($A$2&"!H:H"),"<.7") <-- This also works just fine where the rows have a number less than .7

I can't get the two to play together nicely. I'd like to count responses where both criteria are met.
Please help.
------------------------------------------------------------------------------------
following is a solution to the problem ... I did not have to make any INDIRECT function call