whats wrong with my formula?

colinheslop1984

Board Regular
Joined
Oct 14, 2016
Messages
129
Office Version
  1. 2016
Hi

need help completing a SUMIFS(INDIRECT function that will gather data from another workbook. I am trying the below formula but it I get a message saying theres an error, but I'm not that great at this sort of stuff.

=SUMIFS('[WEEKLY SALES PERFORMANCE.xlsx]'INDIRECT("'"!$H:$H),'[WEEKLY SALES PERFORMANCE.xlsx]'INDIRECT("'"!$B:$B,$O$1),'[WEEKLY SALES PERFORMANCE.xlsx]'INDIRECT("'"!$C:$C,"<="&$K$1))

Can someone correct me please
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
see if this work? note that the WEEKLY SALES PERFORMANCE.xlsx needed to be open for the indirect() to work

Code:
=SUMIFS(INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]'!"&$H:$H),INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]'!"&$B:$B,$O$1),INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]'!"&$C:$C,"<="&$K$1))
 
Upvote 0
Sorry I missed something off...

=SUMIFS(INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]'!"'"c1"&$H:$H),INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]'!"'"c1"&$B:$B,$O$1),INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]'!"'"c1"&$C:$C,"<="&$K$1))

c1 contains the year to look up, this is to find the desired worksheet name from the weekly sale performance workbook.

I want to SUM H:H where b:b = o1 and where c:c <=k1
 
Upvote 0
try

Code:
=SUMIFS(INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]'!C1"&$H:$H),INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]'!C1"&$B:$B,$O$1),INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]'!C1"&$C:$C,"<="&$K$1))
 
Upvote 0
it just seems to be referencing the current sheet for h:h, c:c, b:b, rather than the weekly sales sheet

Is there another way maybe to perform the same calculation?
 
Upvote 0
Sorry I missed something off...

=SUMIFS(INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]'!"'"c1"&$H:$H),INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]'!"'"c1"&$B:$B,$O$1),INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]'!"'"c1"&$C:$C,"<="&$K$1))

c1 contains the year to look up, this is to find the desired worksheet name from the weekly sale performance workbook.

I want to SUM H:H where b:b = o1 and where c:c <=k1

try this

Code:
=SUMIFS(INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]"&C1&"'!$H:$H"),INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]"&C1&"'!$B:$B"),"=o1",INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]"&C1&"'!$C:$C"),"<="&K1)
 
Upvote 0
try

Code:
=SUMIFS(INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]'!C1"&$H:$H),INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]'!C1"&$B:$B,$O$1),INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]'!C1"&$C:$C,"<="&$K$1))

just noticed that O1 is a cell reference, formula should be

Code:
=SUMIFS(INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]"&C1&"'!$H:$H"),INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]"&C1&"'!$B:$B"),[COLOR="#FF0000"]$O$1[/COLOR],INDIRECT("'[WEEKLY SALES PERFORMANCE.xlsx]"&C1&"'!$C:$C"),"<="&$K$1)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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?

Disable AdBlock

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
Back
Top