INDIRECT informulas

[no_name]

I have a cell which has the code

=INDIRECT("'" & Summary!A18 & "'!" & "A23") in it.

I want to use this value in a COUNTIFS

what should the syntax be?


Thanks in advance




--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-

 

[Spencer101]

I have a cell which has the code

=INDIRECT("'" & Summary!A18 & "'!" & "A23") in it.

I want to use this value in a COUNTIFS

what should the syntax be?


Thanks in advance

You don't make it clear in your question exactly what you're trying to
do.
Do you want to use the value that results from the INDIRECT formula
above as the value to count in the COUNTIF, or to identify the range in
which to count a value??

Explain more of what you're trying to do and an answer will be provided
much quicker.

S.

 

[joeu2004]

I have a cell which has the code
=INDIRECT("'" & Summary!A18 & "'!" & "A23") in it.
I want to use this value in a COUNTIFS
what should the syntax be?

Perhaps:

COUNTIFS(INDIRECT("'" & Summary!A18 & "'!" & "A1:A1000"),B1,C1:C1000,D1)

or

COUNTIFS(B1:B1000,INDIRECT("'" & Summary!A18 & "'!" & "A23"),C1:C1000,D1)

Provide a concrete example that explains what you want to do. The more
specific your question, the more specific the answer.

But in any case, be careful what you wish for.

Since INDIRECT is a volatile function, your COUNTIFS formula will be
recalculated, along with any directly or indirectly dependent formulas,
every time you edit __any__ cell in __any__ worksheet in the workbook.

This can be slow things down significantly, especially if you use
whole-column references (not recommended) in the COUNTIFS formula, like:

COUNTIFS(B:B,INDIRECT("'" & Summary!A18 & "'!" & "A23"),C:C,D1)