Drag down matrix function

[Friede]

Dear all,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I have a matrix function and I need to use the same function all the time. Just one cell in the formula should change. Excel does not recognize that only this one cell is changing when I just drag it down, but also 1:190 changes to 2:191 and so on. I do not know how to use the $-signs in a matrix function for this purpose.
Here is the function I am using:
=SUMPRODUCT(--(COUNTIF(INDIRECT("Respondent"&ROW(1:190)& "!$B$8");1));--(COUNTIF(INDIRECT("Respondent"&ROW(1:190)& "!B11");1)))
<o> </o>
The bold cell is the only cell which should change all the time from B11 to C11 and so on. The rest should be fixed that I can just drag the formula down.
<o> </o>
Thanks in advance for your answers.

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(
--(COUNTIF(INDIRECT("Respondent"&ROW($1:$190)& "!B8");1));
--(COUNTIF(INDIRECT("Respondent"&ROW($1:$190)& "!"&
     CELL("address";B11)));1)))

 

[Friede]

Thanks. Still I cannot drag it down if I have put three or more formulas below each other. The one cell always stays B11 (bolt) and won't go to B12 and so on.

=SUMMENPRODUKT(--(ZÄHLENWENN(INDIREKT("Respondent"&ZEILE($1:$190)& "!$B$8");1));--(ZÄHLENWENN(INDIREKT("Respondent"&ZEILE($1:$190)& "!B11");1)))

Does somebody knows a solution?
Thx in advance

 

[Aladin Akyurek]

Thanks. Still I cannot drag it down if I have put three or more formulas below each other. The one cell always stays B11 (bolt) and won't go to B12 and so on.

=SUMMENPRODUKT(--(ZÄHLENWENN(INDIREKT("Respondent"&ZEILE($1:$190)& "!$B$8");1));--(ZÄHLENWENN(INDIREKT("Respondent"&ZEILE($1:$190)& "!B11");1)))

Does somebody knows a solution?
Thx in advance

Did you try what I proposed? You seem to have missed the CELL bit.

 

[Friede]

True story!!! Now it is working perfectly!

Thanks a lot. Have a nice evening in the Hague!

 

[Aladin Akyurek]

True story!!! Now it is working perfectly!

Thanks a lot. Have a nice evening in the Hague!

You are welcome. Thanks for the nice feedback.