# Desperate for a solution

Status
Not open for further replies.

#### colinheslop1984

##### Board Regular
=SUMIFS(INDIRECT("'"&\$C\$3&"'!h2:h365"),INDIRECT("'"&\$C\$3&"'!c2:c365",G3),INDIRECT("'"&\$C\$3&"'!d2:d365",A13))

This formula returns a value of '0', instead of 40

When I remove the indirect function, i.e

=SUMIFS('2019'!H2:H365,'2019'!C2:C365,"="&G3,'2019'!D2:D365,"="&A11)

I get the value 40, which is right.

Anyone who can solve that for me would be appreciated

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
as i said in the last post here
https://www.mrexcel.com/forum/excel-questions/1090235-sumifs-using-indirect-2.html#post5240065
this is wrong
=SUMIFS(INDIRECT("'"&\$C\$3&"'!h2:h365"),INDIRECT("'"&\$C\$3&"'!c2:c365",G3),INDIRECT("'"&\$C\$3&"'!d2:d365",A13))

I have even created a file and loaded on dropbox for you, that is working

=SUMIFS(INDIRECT("'"&\$C\$3&"'!h2:h365"),INDIRECT("'"&\$C\$3&"'!c2:c365"),G3,INDIRECT("'"&\$C\$3&"'!d2:d365"),A11)
Works in my sample file

or as you new formula

=SUMIFS(INDIRECT("'"&\$C\$3&"'!H2:H365"),INDIRECT("'"&\$C\$3&"'!C2:C365"),\$G\$3,INDIRECT("'"&\$C\$3&"'!D2:D365"),A13)

Last edited:
Status
Not open for further replies.

Replies
3
Views
233
Replies
1
Views
555
Replies
3
Views
312
Replies
0
Views
327
Replies
12
Views
310

1,203,667
Messages
6,056,645
Members
444,879
Latest member
suzndush

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