whats wrong with my formula?

colinheslop1984

Board Regular
Joined
Oct 14, 2016
Messages
113
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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))
 

colinheslop1984

Board Regular
Joined
Oct 14, 2016
Messages
113
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
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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))
 

colinheslop1984

Board Regular
Joined
Oct 14, 2016
Messages
113

ADVERTISEMENT

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?
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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)
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

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
Top