whats wrong with my formula?

[colinheslop1984]

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

 

[AlanY]

see if this work? note that the WEEKLY SALES PERFORMANCE.xlsx needed to be open for the indirect() to work

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

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]

try

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

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]

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

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

 

[colinheslop1984]

Perfect!! Thank you

 

[AlanY]

you're welcome

 

[AlanY]

try

=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

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