CountIF with multiple criteria... couple of problems driving me nuts, help?

[rtilghman]

I have an interview analysis tool I've built and need the ability to
have second level analysis for the different questions. This is
basically on the level of "how many people who answered X also
answered Y?". Ideally I would be able to do this in three dimensions,
but I can probably deal with two if need be.

QUESTION 1 - What is wrong with my 2D formula?

Found some discussion about doing this with SUMPRODUCT, implemeneted
it, and got it to work alright. However, for some reason my
validation just won't work correctly. Here's a sample table:

Answer 2A Answer 2B Answer 2C
Answer 1A
Answer 1B
Answer 1C

And here's the formula:

=IF(AND(ISTEXT($A79),ISTEXT(I$97)),SUMPRODUCT(($K$8:$K$73=$A79)*($C
$8:$C$73=I$97)),"")

(Note the numbers seem wierd because the columns being tested aren't
actually in the same table like the example... there is one table and
then a unified table down the page).

Basically it tests for key labels for the two criteria (which in one
case is called into the current sheet from a secondary location) and
if it finds both (meaning there are criteria for those two columns)
runs the routine in that cell.

The only thing I can think is that Cell I97 is not "technically"
empty... it has a forumla. However, that resolves and would come up
as nothing if its blank, right?

QUESTION 2 - Can I do this in three dimensions?

Right now I'm testing the variables I've already pulled into the
sheet. The question is if I can target vairables in the interview
sheets themselves (each interview has a sheet, and there's an analysis
page for each question)...? Thoughts? Is it possible to successfully
do a multi-factor COUNTIF across multiple sheets testing for the two
factors in each sheet?

Thanks for any help... I'm not much of an Excel whiz and these heavy
duty 3D calcs are frying my brain.

-rt

 

[Gary Brown]

Chip Pearson's website should help you with your question.

http://www.cpearson.com/excel/array.htm

--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[rtilghman]

Chip Pearson's website should help you with your question.

http://www.cpearson.com/excel/array.htm

Thanks for the reply. I actually figured out my mistake with the
first one... guess was right, didn't realize formulas count as
values. Changed the test routine and it worked fine.

With regard to the link thanks, I'll take a look. I imagine there's
got to be a way to do the same thing three-dimensionally, I just need
some examples to lead me in the right direction. The routines I write
always seem to fail when they try to test the values in the DIRECT
call... they make the array fine, but collapse with VALUE when they
try to import the values for analysis.

-rt