IF statement contain Indirect to point in another sheet

[Sam80]

Hello all,
I am a bit confused about a test that I did in Excel. If anyone has an explanation please let me know it!

I have two sheets. one sheet is data ( named as data) and the other is calculation ( named as calc).

If in a cell of 'calc' sheet I write the below code I will get #Value! when I use evaluate formula (But excel shows the correct answer) when it reach of calculation of Data!$A$1:$A$12. This happens if and only if I use indirect with the reference to the other sheet.


=SUM(IF(INDIRECT("'Data'!$A$4"):INDIRECT("'Data'!$A$12")="a";1;0))

Thank you for your support!

 

[Oeldere]

=SUM(IF(INDIRECT("'Data'!$A$4")&":"&INDIRECT("'Data'!$A$12")="a";1;0))

 

[Aladin Akyurek]

Hello all,
I am a bit confused about a test that I did in Excel. If anyone has an explanation please let me know it!

I have two sheets. one sheet is data ( named as data) and the other is calculation ( named as calc).

If in a cell of 'calc' sheet I write the below code I will get #Value! when I use evaluate formula (But excel shows the correct answer) when it reach of calculation of Data!$A$1:$A$12. This happens if and only if I use indirect with the reference to the other sheet.


=SUM(IF(INDIRECT("'Data'!$A$4"):INDIRECT("'Data'!$A$12")="a";1;0))

Thank you for your support!

=SUM(IF(INDIRECT("Data!A4:A12")="a";1;0))

This formula must be confirmed with control+shift+enter, not just enter.

But do you really need such for

=SUM(IF(Data!$A$4:$A$12="a";1;0)

would suffice (also control+shift+enter).

Even better, just enter:

=COUNTIF(Data!A4:A12;"a")

because a tad faster.

 

[Sam80]

=SUM(IF(INDIRECT("'Data'!$A$4")&":"&INDIRECT("'Data'!$A$12")="a";1;0))

Hello Oeldere,
The code that you wrote makes string from the result of first and second indirect and not a reference to the cell data of the 'data' sheet. So that does not work.
is there any other suggestion?
Thx

 

[Sam80]

=SUM(IF(INDIRECT("Data!A4:A12")="a";1;0))

This formula must be confirmed with control+shift+enter, not just enter.

But do you really need such for

=SUM(IF(Data!$A$4:$A$12="a";1;0)

would suffice (also control+shift+enter).

Even better, just enter:

=COUNTIF(Data!A4:A12;"a")

because a tad faster.

Hello Aladin,
My aim is to use two Indirect to reference to a cell value in the other sheet as the code that I wrote and of course I use Control Shift Enter for array calculations. what you had written used on indirect that I knew it works.
About countIf yes I know it is possible to do that with other methods but the aim is to get why Evaluation Formula gives #Value! when it wants to interpret the the indirects from left handside of the equal sign with the right hand sign

 

[Aladin Akyurek]

Hello Aladin,
My aim is to use two Indirect to reference to a cell value in the other sheet as the code that I wrote and of course I use Control Shift Enter for array calculations. what you had written used on indirect that I knew it works.
About countIf yes I know it is possible to do that with other methods but the aim is to get why Evaluation Formula gives #Value! when it wants to interpret the the indirects from left handside of the equal sign with the right hand sign

What does give the #VALUE! error? I mean which formula...