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

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

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

=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.]

On 14 Mar, 05:34, wrote:
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