SUMIF formula with INDIRECT and LEFT as reference

[AndreK123]

I need to sum up data using the first three letters as the ref point across multiple sheets, I have the current formula but it does not work, any other options?
Thank you

=SUMIF(LEFT(INDIRECT("'PL "&R$16&"'!$A:$A"),3),'Monthly Pos PnL Admin Data-->'!$A61,INDIRECT("'PL "&R$16&"'!$G:$G"))

The INDIRECT function references a monthly sheet where the data exists but need to be able to drag across to pick up data from multiple months which is why using INDIRECT function

Any help appreciated!!

 

[Special-K99]

What does "it does not work" mean?

You are getting an error? Which error?
You are not getting the results you expect?

Are the files you refer to actually open?

 

[AndreK123]

I just get a general "there is a problem with the formula"
The sheets are open as they are in the same excel book

So for example, I have a sheet for January 2016 called Jan 16 where I want to pick up all the values relating to a securities where the first three letters start with HYG
Then I need the formula to summarise the profit made for those same securities for February 2016 in a sheet called Feb 16 which is why I am trying to automate it so I can drag across for all the months

Hopefully that is clearer?

 

[Special-K99]

Am certain you cant do this

LEFT(INDIRECT("'PL "&R$16&"'!$A:$A"),3)

LEFT of the entire column A ?
LEFT only works on an individual string.

 

[Marcelo Branco]

SUMIF requires a range as its first argument.

Try SUMPRODUCT. Something like
=SUMPRODUCT(--(LEFT(INDIRECT(.....),3)="HYG"),INDIRECT(....))

Remark: avoid references to entire columns, like A:A or G:G, in SUMPRODUCT. Use definite ranges, for example, A1:A1000 and G1:G1000 - adjust to suit.

M.

 

[Special-K99]

Try replacing it with

ISNUMBER(SEARCH("HYG*"...)

 

[Marcelo Branco]

Another

=SUMIF(INDIRECT("'PL "&R$16&"'!$A:$A"),'Monthly Pos PnL Admin Data-->'!$A61&"*",INDIRECT("'PL "&R$16&"'!$G:$G"))

where 'Monthly Pos PnL Admin Data-->'!$A61 = HYG

M.

 

[AndreK123]

Excellent - thank you very much! The last one worked great!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.