Sumproduct, two factors, wildcard, and three dimensions... is there a way?

[rtilghman]

So a few days ago I posted regarding a script I was trying to get
working to give me two-part analysis of cells in a sheet. Bob
Phillips posted with a working script that used the ISNUMBER/FIND
method for doing the same thing as using straight arrays (which I was
doing previously).

The solution is great, but I have one lingering problem; I'd really
like the ability to do this same calculation three dimensionally
(multiple sheets) as opposed to two dimensionally (single sheet).
FIND, as I've discovered in my brief Excel ramp-up, only works in two-
dimenions in formulas...

While I could pull all the results I need into a single sheet, I'm
dealing with 66 interviews that have 45 questions each, and the amount
of data I would need to aggregate to one sheet to do it would be
enormous.

So, is there any way to make the following formula three dimensional?

=IF(AND($A$79<>"",E$99<>""),IF($E$94<>0,
SUMPRODUCT((ISNUMBER(FIND($A$79,$K$8:$K$74)))*
(ISNUMBER(FIND(E$99,$C$8:$C$74)))/$E$94),0),"")

If anyone has any thoughts I'd appreciate. I'd like to do this in
formula if possible, but any ideas or insights are welcome.

Thanks,
Rick

 

[Lori]

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Shts&"'!k"&ROW(K8:K74)),
TEXT(A79,"0;-0;¦;\*@\*"))*COUNTIF(INDIRECT("'"&Shts&"'!
c"&ROW(C8:C74)),
TEXT(E99,"0;-0;¦;\*@\*"))/TEXT(N(INDIRECT("'"&Shts&"'!
e94")),"0;-0;9\e307"))

maybe? Where "Shts" is a defined name referring to "=get.workbook(1)"
for all sheets in the workbook, or otherwise to a row of cells
containing the sheet names.

[The Text(...) functions provide a compact way to deal with the "if"
conditions.]