# IF statement contain Indirect to point in another sheet

#### Sam80

##### New Member
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))

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Oeldere

##### Well-known Member
=SUM(IF(INDIRECT("'Data'!\$A\$4")&":"&INDIRECT("'Data'!\$A\$12")="a";1;0))

##### MrExcel MVP
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))

=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")

#### Sam80

##### New Member
=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

##### New Member
=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")

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 ##### MrExcel MVP
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...

Replies
6
Views
750
Replies
2
Views
218
Replies
1
Views
2K
Replies
7
Views
190
Replies
6
Views
309

### Forum statistics

1,191,006
Messages
5,984,126
Members
439,872
Latest member
noaman79 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back