problem with Array Formula
 

I have experienced a problem with an array formula

I am combining a SUM(IF) formula with an INDIRECT formula due to the
column that is being counted is variable depending on other
conditions.

Here is the Formula as it reads now

{=SUM((IF(INDIRECT(A87&"2:"&RIGHT(A87)&"500")="R1" ,1,0)))}

A87 is the cell that references the sheet and column to read.

Right now the Cell reads
Sheet1!AE

The problem lies in that if there is an "R1" in column Sheet1!F it
counts that one also!

Can anyone help in fixing this error?

Thank you


--
OrdOff
------------------------------------------------------------------------
OrdOff's Profile: http://www.excelforum.com/member.php...o&userid=22708
View this thread: http://www.excelforum.com/showthread...hreadid=383550

Try...

{=SUM((IF(INDIRECT(A87&"2:"&RIGHT(A87,LEN(A87)-FIND("!",A87))&"500")="R1",1,0)))}

If you enter the names for the sheet and column in two separate cells,
you can use the following formula instead...

{=SUM((IF(INDIRECT("'"&A87&"'!"&B87&"2:"&B87&"500" )="R1",1,0)))}

...where A87 contains the sheet name, such as Sheet1, and B87 contains
the column of interest, such as AE.

Hope this helps!

OrdOff Wrote:
I have experienced a problem with an array formula

I am combining a SUM(IF) formula with an INDIRECT formula due to the
column that is being counted is variable depending on other
conditions.

Here is the Formula as it reads now

{=SUM((IF(INDIRECT(A87&"2:"&RIGHT(A87)&"500")="R1" ,1,0)))}

A87 is the cell that references the sheet and column to read.

Right now the Cell reads
Sheet1!AE

The problem lies in that if there is an "R1" in column Sheet1!F it
counts that one also!

Can anyone help in fixing this error?

Thank you


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=383550

=SUM((IF(INDIRECT(A87&"2:"&RIGHT(A87)&"500")="R1", 1,0)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"OrdOff" wrote in
message ...

I have experienced a problem with an array formula

I am combining a SUM(IF) formula with an INDIRECT formula due to the
column that is being counted is variable depending on other
conditions.

Here is the Formula as it reads now

{=SUM((IF(INDIRECT(A87&"2:"&RIGHT(A87)&"500")="R1" ,1,0)))}

A87 is the cell that references the sheet and column to read.

Right now the Cell reads
Sheet1!AE

The problem lies in that if there is an "R1" in column Sheet1!F it
counts that one also!

Can anyone help in fixing this error?

Thank you


--
OrdOff
------------------------------------------------------------------------
OrdOff's Profile:
http://www.excelforum.com/member.php...o&userid=22708
View this thread: http://www.excelforum.com/showthread...hreadid=383550