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

Thank you for your support!

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
=SUM(IF(INDIRECT("'Data'!\$A\$4")&":"&INDIRECT("'Data'!\$A\$12")="a";1;0))

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.

=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

=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.

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

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
1
Views
285
Replies
10
Views
325
Replies
1
Views
356
Replies
1
Views
133
Replies
3
Views
948

1,221,206
Messages
6,158,526
Members
451,497
Latest member
something68

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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